/*
 * Created on Jun 10, 2004
 */
package com.scalar.chiptrack.operations.reports.dao;

import java.sql.*;
import java.util.*;
import java.util.Date;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.LotView;
import com.scalar.chiptrack.operations.process.Product;
import com.scalar.chiptrack.operations.reports.WipFlush;
import com.scalar.chiptrack.operations.reports.WipFlushByDate;
import com.scalar.chiptrack.operations.reports.WipFlushByMonth;
import com.scalar.chiptrack.operations.reports.WipQuantity;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.WorkWeek;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.adaptor.AdaptorConstants;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @author Ravikanth
**/
public class WipFlushDAO implements AdaptorConstants
{
	private static int WIP_INVENTORY_UNITS = 1;
	private static int WIP_SHIPPABLE_UNITS = 2;
	private static int WIP_UNALLOCATED_UNITS = 3;
	private static int WIP_ALLOCATED_UNITS = 4;

	public static ArrayList getShippableUnitsByMonth(int[] baseProductIds, int vendorId, int flag, String holdLot, int[] fgProcessStepIds) throws ScalarException
	{
		ArrayList baseProductList = new ArrayList();
		DBTransaction dbTransaction = null;

		try
		{
			// Start transaction
			//dbTransaction = DBTransaction.getInstance();
			//dbTransaction.begin();

			// Get all the Base Products into the HashMap
			baseProductList = fetchBaseProductListByMonth( dbTransaction, baseProductIds, vendorId );

			// Set the Vendors Map into ChildMap the BaseProductList
			fetchVendorLotInformationByMonth( dbTransaction, baseProductList, flag, holdLot, fgProcessStepIds);

			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
			}

			// Set the Quantities at all WipFlush Objects
			setQuantityInAllByMonth( baseProductList );

			// Remove all products where the WIP Quantity does not exist
			setRemoveProductsWithoutQtyByMonth( baseProductList );

			// To Show results
			// showValuesByMonth( baseProductList );
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Wip information could not be fetched" );
		}
		finally
		{
			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
			}
		}

		return baseProductList;
	}

	public static ArrayList getShippableUnitsByMonthByVendor(int[] baseProductIds, int vendorId, int flag, String holdLot, int[] fgProcessStepIds) throws ScalarException
	{
		ArrayList baseProductList = new ArrayList();
		DBTransaction dbTransaction = null;
		ArrayList vendorProductList= new ArrayList();

		try
		{
			// Start transaction
			//dbTransaction = DBTransaction.getInstance();
			//dbTransaction.begin();

			// Get all the Base Products into the HashMap
			baseProductList = fetchBaseProductListByMonth( null /*dbTransaction*/, baseProductIds, vendorId );

			// Set the Vendors Map into ChildMap the BaseProductList
			fetchVendorLotInformationByMonth( dbTransaction, baseProductList, flag, holdLot, fgProcessStepIds);

			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
				dbTransaction = null;
			}

			// Make Vendor HashMap
			vendorProductList = fetchVendorMapByMonth( baseProductList );

			// Set the Quantities at all WipFlush Objects
			setQuantityInAllByMonth( vendorProductList );

			// Remove all products where the WIP Quantity does not exist
			setRemoveProductsWithoutQtyByMonth( vendorProductList );

			// To Show results
			// showValuesByMonth( vendorProductList );
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Wip information could not be fetched" );
		}
		finally
		{
			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
			}
		}

		return vendorProductList;
	}

	/**
     * @param baseProductList
     * @return
     */
    private static ArrayList fetchVendorMapByMonth( ArrayList baseProductList )
    {
		ArrayList vendorList = new ArrayList();
		HashMap finalVendorMap = new HashMap();
		LinkedHashMap finalBaseProductMap = null;
		LinkedHashMap finalProductMap = null;
		LinkedHashMap finalLotMap = null;

		WipFlushByMonth finalBaseproduct = null;
		WipFlushByMonth finalProduct 	 = null;
		WipFlushByMonth finalVendor 	 = null;

		WipFlushByMonth baseproduct = null;
		WipFlushByMonth product 	 = null;
		WipFlushByMonth vendor 	 = null;

		HashMap products 	= null;
		HashMap vendorMap 	= null;
		LinkedHashMap lotMap= null;

		Iterator productIt  = null;
		Iterator vendorIt 	= null;

		if( baseProductList != null && baseProductList.isEmpty() == false )
		{
			for( int i = 0; i < baseProductList.size(); i++ )
			{
				baseproduct = (WipFlushByMonth)baseProductList.get( i );
				products 	= baseproduct.getChildMap();

				if( products != null )
				{
					productIt = products.keySet().iterator();
					while( productIt.hasNext() )
					{
						product = (WipFlushByMonth)products.get( productIt.next() );
						vendorMap = product.getChildMap();
						if( vendorMap != null )
						{
							vendorIt = vendorMap.keySet().iterator();
							while( vendorIt.hasNext() )
							{
								vendor = (WipFlushByMonth)vendorMap.get( vendorIt.next() );
								lotMap = vendor.getChildMap();
								finalBaseproduct = null;
								finalProduct = null;

								if( finalVendorMap.containsKey( vendor.getVendor() ) )
								{
									finalVendor = (WipFlushByMonth)finalVendorMap.get( vendor.getVendor() );
									finalBaseProductMap = finalVendor.getChildMap();

									if( finalBaseProductMap != null && finalBaseProductMap.containsKey( baseproduct.getPartNumber() ) )
									{
										finalBaseproduct = (WipFlushByMonth)finalBaseProductMap.get( baseproduct.getPartNumber() );
										finalProductMap = finalBaseproduct.getChildMap();

										if( finalProductMap != null && finalProductMap.containsKey( product.getPartNumber() ) )
										{
											finalProduct = (WipFlushByMonth)finalProductMap.get( product.getPartNumber() );
											finalLotMap  = finalProduct.getChildMap();
											addLotsByLotNumber( finalLotMap, lotMap );
											finalProduct.setChildMap( finalLotMap );
										}
										else
										{
											if( finalProductMap == null )
											{
												finalProductMap = new LinkedHashMap();
											}

											finalProduct = new WipFlushByMonth();
											finalProduct.setPartNumber( product.getPartNumber() );
											finalProductMap.put( product.getPartNumber(), finalProduct );
											finalBaseproduct.setChildMap( finalProductMap );
											finalProduct.setChildMap( lotMap );
										}
									}
									else
									{
										if( finalBaseProductMap == null )
										{
											finalBaseProductMap = new LinkedHashMap();
										}

										finalBaseproduct = new WipFlushByMonth();
										finalBaseproduct.setPartNumber( baseproduct.getPartNumber() );
											finalBaseProductMap.put( baseproduct.getPartNumber(), finalBaseproduct );
										finalVendor.setChildMap( finalBaseProductMap );

										finalProductMap = new LinkedHashMap();
										finalProduct = new WipFlushByMonth();
										finalProduct.setPartNumber( product.getPartNumber() );
										finalProduct.setChildMap( lotMap );
										finalProductMap.put( product.getPartNumber(), finalProduct );
										finalBaseproduct.setChildMap( finalProductMap );
									}
								}
								else
								{
									//System.out.println( "Vendor Name "+ vendor.getVendor() );
									finalVendor = new WipFlushByMonth();
									finalVendor.setVendor( vendor.getVendor() );
									finalVendorMap.put( vendor.getVendor(), finalVendor );

									finalBaseProductMap = new LinkedHashMap();
									finalBaseproduct = new WipFlushByMonth();
									finalBaseproduct.setPartNumber( baseproduct.getPartNumber() );
									finalBaseProductMap.put( baseproduct.getPartNumber(), finalBaseproduct );
									finalVendor.setChildMap( finalBaseProductMap );

									finalProductMap = new LinkedHashMap();
									finalProduct = new WipFlushByMonth();
									finalProduct.setPartNumber( product.getPartNumber() );
									finalProduct.setChildMap( lotMap );
									finalProductMap.put( product.getPartNumber(), finalProduct );
									finalBaseproduct.setChildMap( finalProductMap );
								}
							}
						}
					}
				}
			}
		}

		Iterator finalVendorIterator = null;

		if( finalVendorMap != null)
		{
			finalVendorIterator = finalVendorMap.keySet().iterator();
			while( finalVendorIterator.hasNext() )
			{
				vendorList.add( finalVendorMap.get( finalVendorIterator.next() ) );
			}
		}

		return vendorList;
    }

    /**
	 * @param dbTransaction
	 * @param baseProductIds
	 * @param vendorId
	 * @return
	 */
	private static ArrayList fetchBaseProductListByMonth( DBTransaction dbTransaction, int[] baseProductIds, int vendorId ) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;

		ArrayList baseProductList = null; // This is the Main ArrayList having all the Base Products Details
		WipFlushByMonth baseProductWip   = null; // This is the WipFlushByMonth Object for the Base Product

		LinkedHashMap productsMap 	= null;   // This is the Sub Map in Base Product Object to carry all the Products under it
		ArrayList productList 	= null;   // Since there can be many products with the same name. This carries all the products with the same name
		Product product 		= null;   // This is the Object to store Information about each Product
		String partNumber 		= null;
		WipFlushByMonth productWip 	= null;	  // This is the WipFlushByMonth Object to store the information of Products
		ArrayList allProductList = null;
		HashMap baseProductsMap = new HashMap();

		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            StringBuffer baseProducts = new StringBuffer();
            if ( baseProductIds != null )
            {
                for ( int i=0; i < baseProductIds.length; i++ )
                {
                    if ( baseProductIds[i] == 0 )
                    {
                        baseProducts = null;
                        break;
                    }
                    else
                    {
                        baseProducts.append( baseProductIds[i] ).append( "," );
                    }
                }
                if ( baseProducts != null )
                {
                    baseProducts.deleteCharAt( baseProducts.lastIndexOf(",") );
                }
            }
            else
            {
                baseProducts = null;
            }
			if( baseProducts == null )
			{
				pstmt = conn.prepareStatement( "Select Product_Id, Part_Number from product (NOLOCK) where P_Parent_Product_Id is null and P_Type is null ORDER BY Part_Number" );
			}
			else
			{
				pstmt = conn.prepareStatement( "Select Product_Id, Part_Number from product (NOLOCK) where  Product_Id IN ( "+ baseProducts.toString() +" ) ORDER BY Part_Number" );
				//pstmt.setInt( 1, baseProductId );
			}

			rs 	  = pstmt.executeQuery();

			while( rs.next() )
			{
				if( baseProductList == null )
				{
					baseProductList = new ArrayList();
				}

				baseProductWip = new WipFlushByMonth();

				baseProductWip.setProductId( rs.getInt( "Product_Id" ) );
				baseProductWip.setPartNumber( rs.getString( "Part_Number" ) );
				baseProductList.add( baseProductWip );
			}

			if ( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}

			if( baseProductList != null && baseProductList.isEmpty() == false )
			{
				for( int i = 0; i < baseProductList.size(); i++ )
				{
					baseProductWip = ( WipFlushByMonth )baseProductList.get( i );
					allProductList = new ArrayList();

					if( vendorId == 0 )
					{
						pstmt = conn.prepareStatement( "select Product_Id, Product.ProcessStep_id, Part_Number, PS_Type, PS_Type_Name, " +
                                " (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = Process_Step.PS_Type) AS PS_Type_Name_Order_No from Product, Process_Step (NOLOCK) where Product.ProcessStep_id = Process_Step.ProcessStep_id and P_Parent_Product_Id = ? order by PS_Type_Name_Order_No, Part_Number" );
						pstmt.setInt( 1, baseProductWip.getProductId() );
					}
					else
					{
						pstmt = conn.prepareStatement( "select Product_Id, Product.ProcessStep_id, Part_Number, PS_Type, PS_Type_Name, " +
                                "(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = Process_Step.PS_Type) AS PS_Type_Name_Order_No from Product, Process_Step, PROCESS_STEP_LOCATION_TABLE, LOCATION (NOLOCK) where Product.ProcessStep_id = Process_Step.ProcessStep_id and Process_Step.ProcessStep_id = PROCESS_STEP_LOCATION_TABLE.ProcessStep_id and PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID and P_Parent_Product_Id = ?  AND VENDOR_ID = ? order by PS_Type_Name_Order_No, Part_Number" );
						pstmt.setInt( 1, baseProductWip.getProductId() );
						pstmt.setInt( 2, vendorId );
					}
					rs = pstmt.executeQuery();

					while( rs.next() )
					{
						product	= new Product();
						product.setProductId( rs.getInt( "Product_Id" ) );
						product.setProcessStepId( rs.getInt( "ProcessStep_id" ) );
						partNumber = rs.getString( "Part_Number" );
						product.setPartNumber( partNumber );
						product.setProcessType( rs.getString( "PS_Type" ) );
						product.setBaseProcessType( rs.getString( "PS_Type_Name" ) );
						allProductList.add( product );
					}

					baseProductsMap.put( baseProductWip.getPartNumber(), allProductList );

					if ( rs != null ){	try { rs.close(); } catch (SQLException ignore) {}	}
					if ( pstmt != null ){	try { pstmt.close(); } catch (SQLException ignore) {}}
				}

				if( baseProductList != null && baseProductList.isEmpty() == false )
				{
					for( int i = 0; i < baseProductList.size(); i++ )
					{
						baseProductWip = ( WipFlushByMonth )baseProductList.get( i );
						productsMap = baseProductWip.getChildMap();
						if( productsMap == null )
						{
							productsMap = new LinkedHashMap();
						}

						if( baseProductsMap.containsKey( baseProductWip.getPartNumber() ) )
						{
							allProductList = (ArrayList)baseProductsMap.get( baseProductWip.getPartNumber() );

							for( int j = 0; j < allProductList.size(); j++ )
							{
								product = (Product)allProductList.get( j );
								partNumber = product.getPartNumber();

								if( productsMap.containsKey( partNumber ) )
								{
									productWip = ( WipFlushByMonth )productsMap.get( partNumber );
									productList = productWip.getProducts();
									productList.add( product );
								}
								else
								{
									productWip = new WipFlushByMonth();
									productWip.setPartNumber( partNumber );
									productList = new ArrayList();
									productList.add( product );
									productWip.setProducts( productList );
									productsMap.put( partNumber, productWip );
									baseProductWip.setChildMap( productsMap );
								}
							}
						}
					}
				}
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Base products could not be fetched" );
		}
		finally
		{
			if ( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if ( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

		return baseProductList;
	}

	/**
	 * @param dbTransaction
     * @param baseProductList
     * @param flag
     * @param fgProcessStepIds
     */
	private static void fetchVendorLotInformationByMonth(DBTransaction dbTransaction, ArrayList baseProductList, int flag, String holdLot, int[] fgProcessStepIds) throws ScalarException
	{
		WipFlushByMonth baseProductWip 	= null;
		WipFlushByMonth productWip 		= null;
		HashMap productsMap 			= null;
		Iterator productsIterator 		= null;
        HashMap shippableVendorWipMap = null;
        LinkedHashMap vendorMap     = null;

        if( baseProductList != null && baseProductList.isEmpty() == false )
		{
            if ( WIP_SHIPPABLE_UNITS == flag )
            {
                int holdType = 1; //ALL
                try{ holdType = Integer.parseInt ( holdLot ); } catch( NumberFormatException e ){ holdType = 1; }
                shippableVendorWipMap = getShippableVendorWipMapByMonth( dbTransaction, holdType, fgProcessStepIds);
            }
            for( int i = 0; i < baseProductList.size(); i++ )
			{
				baseProductWip 	= ( WipFlushByMonth ) baseProductList.get( i );
				productsMap 	= ( HashMap )baseProductWip.getChildMap();
				if( productsMap != null && productsMap.isEmpty() == false )
				{
					productsIterator = productsMap.keySet().iterator();
					while( productsIterator.hasNext() )
					{
						productWip = (WipFlushByMonth)productsMap.get( productsIterator.next() );
                        if( WIP_SHIPPABLE_UNITS == flag )
				        {
                            vendorMap = (LinkedHashMap)shippableVendorWipMap.get ( productWip.getPartNumber() );
                            if ( vendorMap == null )
                            {
                                vendorMap = new LinkedHashMap();
                            }
                            productWip.setChildMap(vendorMap);
                        }
                        else
                        {
                            fetchDataByMonth( dbTransaction, productWip, flag, holdLot );
                        }
                    }
				}
			}
		}
	}

	/**
	 * @param dbTransaction
	 * @param productWip
	 * @param flag
	 */
	private static void fetchDataByMonth( DBTransaction dbTransaction, WipFlushByMonth productWip, int flag, String holdLot ) throws ScalarException
	{
		ArrayList productList 	= null;
		Product product 		= null;
		int processStepId 		= 0;
		LinkedHashMap childMap 	= new LinkedHashMap();

		productWip.setChildMap( childMap );
		productList = productWip.getProducts();

		if( productList != null && productList.isEmpty() == false )
		{
			for( int i = 0; i < productList.size(); i++ )
			{
				product = (Product)productList.get( i );
				processStepId = product.getProcessStepId();

				setShippableLotDataByMonth( dbTransaction, processStepId, childMap, flag, holdLot );
			}
		}
	}

	/**
	 * @param dbTransaction
	 * @param processStepId
	 * @param vendorMap
	 * @param flag
     * @param holdLot
	 */
	private static void setShippableLotDataByMonth(DBTransaction dbTransaction, int processStepId, HashMap vendorMap, int flag, String holdLot) throws ScalarException
	{
		WipFlushByMonth vendorWip = null;
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;

		ArrayList wipList 		= new ArrayList();
		WipQuantity wip 		= null;

		LinkedHashMap lotMap	= null;
		WipFlushByMonth lotWip 	= null;
		String vendorName		= null;
		String lotNumber 		= null;
		Date fg_date			= null;
		int fgQty 				= 0;
		int lotId				= 0;

		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			if( processStepId != 0 )
			{
				if( holdLot == null || holdLot.equalsIgnoreCase( "1" )  )
				{
					//pstmt = conn.prepareStatement( "select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? " );
					if( WIP_SHIPPABLE_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						//pstmt = conn.prepareStatement( "select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) AS FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? " );
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) AS FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_ALLOCATED_UNITS == flag )
					{
						//pstmt = conn.prepareStatement( "select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated AS FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? " );
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated AS FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
				}
				else if ( holdLot.equalsIgnoreCase( "2" ) )
				{
					if( WIP_SHIPPABLE_UNITS == flag )
					{
						//pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where	Wip_Master.Lot_id = Lot_table.Lot_Id and Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and Lot_Table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, lot_table, Location where Wip_Master.Lot_Id = Lot_table.Lot_Id and Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and Lot_Table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' group by Wip_Master.lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						//pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) AS FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) as FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_Table, Location (NOLOCK) where Wip_Master.Lot_Id = Lot_Table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Lot_Table, Location where Wip_Master.Lot_Id = Lot_Table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' group by Wip_Master.lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_ALLOCATED_UNITS == flag )
					{
						//pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated AS FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated as FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_Table, Location (NOLOCK) where Wip_Master.Lot_id = Lot_table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Lot_Table, Location where Wip_Master.Lot_id = Lot_table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' group by Wip_Master.lot_id ) as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
				}
				else
				{
					if( WIP_SHIPPABLE_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where	Wip_Master.Lot_id = Lot_table.Lot_Id and Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, lot_table, Location where Wip_Master.Lot_Id = Lot_table.Lot_Id and Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) group by Wip_Master.lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) as FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_Table, Location (NOLOCK) where Wip_Master.Lot_Id = Lot_Table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Lot_Table, Location where Wip_Master.Lot_Id = Lot_Table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) group by Wip_Master.lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_ALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated as FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_Table, Location (NOLOCK) where Wip_Master.Lot_id = Lot_table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Lot_Table, Location where Wip_Master.Lot_id = Lot_table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) group by Wip_Master.lot_id ) as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
				}
				pstmt.setInt( 1, processStepId );
				pstmt.setInt( 2, processStepId );
				rs 	  = pstmt.executeQuery();

				while( rs.next() )
				{
					vendorName 	= null;
					lotId 		= rs.getInt( "Lot_Id" );
					lotNumber 	= rs.getString( "Lot_Number" );
					vendorName 	= rs.getString( "L_Vendor_Name" );
					fgQty 		= rs.getInt( "FG_Qty" );
					fg_date 	= rs.getDate( "Expected_Date_Out" );

					// If Finished goods date is null set the date to present date
					if( fg_date == null )
					{
						fg_date = new Date();
					}

					if( fgQty > 0 )
					{
						wip = new WipQuantity( lotId, lotNumber, vendorName, fgQty, fg_date );
						wipList.add( wip );
					}
				}

				if ( rs != null )
				{
					try { rs.close(); } catch (SQLException ignore) {}
				}
				if ( pstmt != null )
				{
					try { pstmt.close(); } catch (SQLException ignore) {}
				}
				if ( conn != null )
				{
					try { conn.close(); } catch (SQLException ignore) {}
				}

				if( wipList != null && wipList.isEmpty() == false )
				{
					for( int i = 0; i < wipList.size(); i++ )
					{
						wip = (WipQuantity)wipList.get( i );

						lotId 		= wip.getLotId();
						lotNumber 	= wip.getLotNumber();
						vendorName 	= wip.getVendorName();
						fgQty 		= wip.getFgQty();
						fg_date 	= wip.getFg_date();

						if( vendorMap.containsKey( vendorName ) )
						{
							vendorWip = (WipFlushByMonth)vendorMap.get( vendorName );
							lotMap = vendorWip.getChildMap();

							// Check wether any lot Data is present
							if( lotMap == null )
							{
								lotMap = new LinkedHashMap();
								vendorWip.setChildMap( lotMap );
							}

							if ( lotMap.containsKey( lotNumber ) )
							{
								lotWip = (WipFlushByMonth)lotMap.get( lotNumber );
								setWipQtyByMonth( lotWip, fg_date, fgQty );
							}
							else
							{
								lotWip = new WipFlushByMonth();
								lotWip.setLotNumber( lotNumber );
								lotMap.put( lotNumber, lotWip );
								setWipQtyByMonth( lotWip, fg_date, fgQty );
							}
						}
						else
						{
							vendorWip = new WipFlushByMonth();
							vendorWip.setVendor( vendorName );
							vendorMap.put( vendorName, vendorWip );

							lotMap = new LinkedHashMap();
							vendorWip.setChildMap( lotMap );

							lotWip = new WipFlushByMonth();
							lotWip.setLotNumber( lotNumber );
							lotMap.put( lotNumber, lotWip );
							setWipQtyByMonth( lotWip, fg_date, fgQty );
						}
					}
				}
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Lot Information could not be fetched" );
		}
		finally
		{
			if ( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if ( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}

	/**
	 * @param lotWip
	 * @param fg_date
	 * @param fgQty
	 */
	private static void setWipQtyByMonth( WipFlushByMonth lotWip, Date fg_date, int fgQty )
	{
		long daysRemainingToExpectedDateOut = 0;
		int monthNumber = 0;
		int presentMonth = 0;
		int yearNumber = 0;
		int presentYear = 0;

		Calendar nowCal = new GregorianCalendar();
		nowCal.setTime( new Date() );
		Date now = new GregorianCalendar(nowCal.get(Calendar.YEAR), nowCal.get(Calendar.MONTH), nowCal.get(Calendar.DATE)).getTime();

		Calendar fgCal = new GregorianCalendar();
		Calendar presentCal = new GregorianCalendar();
		fgCal.setTime( fg_date );

		daysRemainingToExpectedDateOut = ( ( fg_date.getTime() - now.getTime() )/( 1000*60*60*24 ) );

		if ( daysRemainingToExpectedDateOut < 0 )
		{
			//monthNumber = nowCal.get( Calendar.MONTH ) + 1;
			monthNumber = 1;
		}
		else
		{
			yearNumber = fgCal.get( Calendar.YEAR );
			presentYear = presentCal.get( Calendar.YEAR );
			presentMonth = presentCal.get( Calendar.MONTH ) + 1;
			monthNumber = fgCal.get( Calendar.MONTH ) + 1 ;

			if( yearNumber < presentYear )
			{
				monthNumber = 1;
			}
			else if ( yearNumber == presentYear )
			{
				if( presentMonth >= monthNumber )
				{
					monthNumber = 1;
				}
				else
				{
					monthNumber = ( monthNumber - presentMonth ) + 1 ;
				}
			}
			else
			{
				monthNumber = ( yearNumber - presentYear )*12 + ( monthNumber - presentMonth ) + 1;
				if( monthNumber > 12 )
				{
					monthNumber = 12;
				}
			}
		}

		switch ( monthNumber )
		{
			case 1:
				lotWip.setMonth1Qty( lotWip.getMonth1Qty() + fgQty );
				break;
			case 2:
				lotWip.setMonth2Qty( lotWip.getMonth2Qty() + fgQty );
				break;
			case 3:
				lotWip.setMonth3Qty( lotWip.getMonth3Qty() + fgQty );
				break;
			case 4:
				lotWip.setMonth4Qty( lotWip.getMonth4Qty() + fgQty );
				break;
			case 5:
				lotWip.setMonth5Qty( lotWip.getMonth5Qty() + fgQty );
				break;
			case 6:
				lotWip.setMonth6Qty( lotWip.getMonth6Qty() + fgQty );
				break;
			case 7:
				lotWip.setMonth7Qty( lotWip.getMonth7Qty() + fgQty );
				break;
			case 8:
				lotWip.setMonth8Qty( lotWip.getMonth8Qty() + fgQty );
				break;
			case 9:
				lotWip.setMonth9Qty( lotWip.getMonth9Qty() + fgQty );
				break;
			case 10:
				lotWip.setMonth10Qty( lotWip.getMonth10Qty() + fgQty );
				break;
			case 11:
				lotWip.setMonth11Qty( lotWip.getMonth11Qty() + fgQty );
				break;
			case 12:
				lotWip.setMonth12Qty( lotWip.getMonth12Qty() + fgQty );
				break;
		}
		lotWip.setTotal( lotWip.getMonth1Qty() + lotWip.getMonth2Qty() + lotWip.getMonth3Qty() + lotWip.getMonth4Qty() + lotWip.getMonth5Qty() + lotWip.getMonth6Qty() + lotWip.getMonth7Qty() + lotWip.getMonth8Qty() + lotWip.getMonth9Qty()+ lotWip.getMonth10Qty() + lotWip.getMonth11Qty() + lotWip.getMonth12Qty() );
	}

	/**
	 * @param baseProductList
	 */
	private static void setQuantityInAllByMonth( ArrayList baseProductList )
	{
		WipFlushByMonth baseproduct = null;
		WipFlushByMonth product 	 = null;
		WipFlushByMonth vendor 	 = null;
		WipFlushByMonth lot 		 = null;

		HashMap products = null;
		HashMap vendorMap 	  = null;
		HashMap lotMap 		  = null;

		Iterator productIt  = null;
		Iterator vendorIt 	= null;
		Iterator lotIt 	  	= null;

		if( baseProductList != null && baseProductList.isEmpty() == false )
		{
			for( int i = baseProductList.size() - 1; i >= 0 ; i-- )
			{
				baseproduct = (WipFlushByMonth)baseProductList.get(i);
				products = baseproduct.getChildMap();

				if( products != null )
				{
					productIt = products.keySet().iterator();
					while( productIt.hasNext() )
					{
						product = (WipFlushByMonth)products.get( productIt.next() );
						vendorMap = product.getChildMap();
						if( vendorMap != null )
						{
							vendorIt = vendorMap.keySet().iterator();
							while( vendorIt.hasNext() )
							{
								vendor = (WipFlushByMonth)vendorMap.get( vendorIt.next() );
								lotMap = vendor.getChildMap();

								if( lotMap != null && lotMap.isEmpty() == false )
								{
									lotIt = lotMap.keySet().iterator();
									while( lotIt.hasNext() )
									{
										lot = (WipFlushByMonth)lotMap.get( lotIt.next() );
										addWipByMonth( vendor, lot );
									}
								}
								addWipByMonth( product, vendor );
							}
						}
						addWipByMonth( baseproduct, product );
					}
				}
			}
		}
	}

	/**
	 * @param mainWip
	 * @param childWip
	 */
	private static void addWipByMonth( WipFlushByMonth mainWip, WipFlushByMonth childWip )
	{
		mainWip.setMonth1Qty( mainWip.getMonth1Qty() + childWip.getMonth1Qty() );
		mainWip.setMonth2Qty( mainWip.getMonth2Qty() + childWip.getMonth2Qty() );
		mainWip.setMonth3Qty( mainWip.getMonth3Qty() + childWip.getMonth3Qty() );
		mainWip.setMonth4Qty( mainWip.getMonth4Qty() + childWip.getMonth4Qty() );
		mainWip.setMonth5Qty( mainWip.getMonth5Qty() + childWip.getMonth5Qty() );
		mainWip.setMonth6Qty( mainWip.getMonth6Qty() + childWip.getMonth6Qty() );
		mainWip.setMonth7Qty( mainWip.getMonth7Qty() + childWip.getMonth7Qty() );
		mainWip.setMonth8Qty( mainWip.getMonth8Qty() + childWip.getMonth8Qty() );
		mainWip.setMonth9Qty( mainWip.getMonth9Qty() + childWip.getMonth9Qty() );
		mainWip.setMonth10Qty( mainWip.getMonth10Qty() + childWip.getMonth10Qty() );
		mainWip.setMonth11Qty( mainWip.getMonth11Qty() + childWip.getMonth11Qty() );
		mainWip.setMonth12Qty( mainWip.getMonth12Qty() + childWip.getMonth12Qty() );
		mainWip.setTotal( mainWip.getTotal() + childWip.getTotal() );
	}

	/**
	 * @param baseProductList
	 */
	private static void setRemoveProductsWithoutQtyByMonth( ArrayList baseProductList )
	{
		WipFlushByMonth baseproduct = null;
		WipFlushByMonth product 	 = null;

		HashMap products = null;

		if( baseProductList != null && baseProductList.isEmpty() == false )
		{
			for( int i = baseProductList.size() - 1; i >= 0 ; i-- )
			{
				baseproduct = (WipFlushByMonth)baseProductList.get(i);
				if( baseproduct.getTotal() == 0 )
				{
					baseProductList.remove( baseproduct );
				}
				else
				{
					products = baseproduct.getChildMap();

					if( products != null && products.isEmpty() == false  )
					{
						Object productKeys[] = products.keySet().toArray();

						for( int j = productKeys.length - 1; j >= 0; j--  )
						{
							product = (WipFlushByMonth)products.get( productKeys[j] );
							if( product.getTotal() == 0 )
							{
								products.remove( productKeys[j] );
							}
						}
					}
					else
					{
						baseProductList.remove( baseproduct );
					}
				}
			}
		}
	}


	//public static ArrayList getShippableUnitsByWeek( int baseProductId, int vendorId, int flag, String holdLot, int noOfWeeks ) throws ScalarException
	public static ArrayList getShippableUnitsByWeek(int[] baseProductIds, int vendorId, int flag, String holdLot, Date wwStartDate, int[] fgProcessStepIds) throws ScalarException
	{
		ArrayList baseProductList = new ArrayList();
		DBTransaction dbTransaction = null;

		try
		{
			// Start transaction
			// dbTransaction = DBTransaction.getInstance();
			// dbTransaction.begin();

			// Get all the Base Products into the HashMap
			baseProductList = fetchBaseProductListByWeek( dbTransaction, baseProductIds, vendorId );

			// Set the Vendors Map into ChildMap the BaseProductList
			fetchVendorLotInformationByWeek( dbTransaction, baseProductList, flag, holdLot, wwStartDate, fgProcessStepIds);

			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
				dbTransaction = null;
			}

			// Set the Quantities at all WipFlush Objects
			setQuantityInAllByWeek( baseProductList );

			// Remove all products where the WIP Quantity does not exist
			setRemoveProductsWithoutQtyByWeek( baseProductList );

			// To Show results
			// showValuesByWeek( baseProductList );
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Wip information could not be fetched" );
		}
		finally
		{
			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
			}
		}

		return baseProductList;
	}

	//public static ArrayList getShippableUnitsByWeekByVendor( int baseProductId, int vendorId, int flag, String holdLot, int noOfWeeks ) throws ScalarException
	public static ArrayList getShippableUnitsByWeekByVendor(int[] baseProductIds, int vendorId, int flag, String holdLot, Date wwStartDate, int[] fgProcessStepIds) throws ScalarException
	{
		ArrayList baseProductList = new ArrayList();
		DBTransaction dbTransaction = null;
		ArrayList vendorProductList= new ArrayList();

		try
		{
			// Start transaction
			//dbTransaction = DBTransaction.getInstance();
			//dbTransaction.begin();

			// Get all the Base Products into the HashMap
			baseProductList = fetchBaseProductListByWeek( dbTransaction, baseProductIds, vendorId );

			// Set the Vendors Map into ChildMap the BaseProductList
			fetchVendorLotInformationByWeek( dbTransaction, baseProductList, flag, holdLot, wwStartDate, fgProcessStepIds);

			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
				dbTransaction = null;
			}

			// Make Vendor HashMap
			vendorProductList = fetchVendorMapByWeek( baseProductList );

			// Set the Quantities at all WipFlush Objects
			setQuantityInAllByWeek( vendorProductList );

			// Remove all products where the WIP Quantity does not exist
			setRemoveProductsWithoutQtyByWeek( vendorProductList );

			// To Show results
			//showValuesByWeek( vendorProductList );
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Wip information could not be fetched" );
		}
		finally
		{
			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
			}
		}

		return vendorProductList;
	}

	/**
	 * @param dbTransaction
	 * @param baseProductIds
	 * @param vendorId
	 * @return
	 */
	private static ArrayList fetchBaseProductListByWeek( DBTransaction dbTransaction, int[] baseProductIds, int vendorId ) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;

		ArrayList baseProductList = null; // This is the Main ArrayList having all the Base Products Details
		WipFlushByDate baseProductWip = null; // This is the WipFlushByDate Object for the Base Product

		LinkedHashMap productsMap 	= null;   // This is the Sub Map in Base Product Object to carry all the Products under it
		ArrayList productList 	= null;   // Since there can be many products with the same name. This carries all the products with the same name
		Product product 		= null;   // This is the Object to store Information about each Product
		String partNumber 		= null;
		WipFlushByDate productWip 	= null;	  // This is the WipFlushByDate Object to store the information of Products

		ArrayList allProductList = null;
		LinkedHashMap baseProductsMap  = new LinkedHashMap();

		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            StringBuffer baseProducts = new StringBuffer();
            if ( baseProductIds != null )
            {
                for ( int i=0; i < baseProductIds.length; i++ )
                {
                    if ( baseProductIds[i] == 0 )
                    {
                        baseProducts = null;
                        break;
                    }
                    else
                    {
                        baseProducts.append( baseProductIds[i] ).append( "," );
                    }
                }
                if ( baseProducts != null )
                {
                    baseProducts.deleteCharAt( baseProducts.lastIndexOf(",") );
                }
            }
            else
            {
                baseProducts = null;
            }
			if( baseProducts == null )
			{
				pstmt = conn.prepareStatement( "Select Product_Id, Part_Number from product (NOLOCK) where P_Parent_Product_Id is null and P_Type is null ORDER BY Part_Number" );
			}
			else
			{
				pstmt = conn.prepareStatement( "Select Product_Id, Part_Number from product (NOLOCK) where Product_Id IN (" + baseProducts + ") ORDER BY Part_Number" );
				//pstmt.setInt( 1, baseProductId );
			}

			rs 	  = pstmt.executeQuery();

			while( rs.next() )
			{
				if( baseProductList == null )
				{
					baseProductList = new ArrayList();
				}

				baseProductWip = new WipFlushByDate();

				baseProductWip.setProductId( rs.getInt( "Product_Id" ) );
				baseProductWip.setPartNumber( rs.getString( "Part_Number" ) );
				baseProductList.add( baseProductWip );
			}

			if ( rs != null ){ try { rs.close(); } catch (SQLException ignore) {} }
			if ( pstmt != null ){ try { pstmt.close(); } catch (SQLException ignore) {}	}

			if( baseProductList != null && baseProductList.isEmpty() == false )
			{
				for( int i = 0; i < baseProductList.size(); i++ )
				{
					baseProductWip = ( WipFlushByDate )baseProductList.get( i );
					allProductList = new ArrayList();

					if( vendorId == 0 )
					{
						pstmt = conn.prepareStatement( "select Product_Id, Product.ProcessStep_id, Part_Number, PS_Type, PS_Type_Name, " +
                                "(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = Process_Step.PS_Type) AS PS_Type_Name_Order_No from Product(NOLOCK), Process_Step (NOLOCK) where Product.ProcessStep_id = Process_Step.ProcessStep_id and P_Parent_Product_Id = ? order by PS_Type_Name_Order_No, Part_Number" );
						pstmt.setInt( 1, baseProductWip.getProductId() );
					}
					else
					{
						pstmt = conn.prepareStatement( "SELECT Product_Id, Product.ProcessStep_id, Part_Number, PS_Type, PS_Type_Name, " +
                                "(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = Process_Step.PS_Type) AS PS_Type_Name_Order_No FROM Product(NOLOCK), Process_Step(NOLOCK), PROCESS_STEP_LOCATION_TABLE(NOLOCK), LOCATION (NOLOCK) WHERE Product.ProcessStep_id = Process_Step.ProcessStep_id AND Process_Step.ProcessStep_id = PROCESS_STEP_LOCATION_TABLE.ProcessStep_id and PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID and P_Parent_Product_Id = ? AND VENDOR_ID = ? order by PS_Type_Name_Order_No, Part_Number" );
						pstmt.setInt( 1, baseProductWip.getProductId() );
						pstmt.setInt( 2, vendorId );
					}
					rs = pstmt.executeQuery();

					while( rs.next() )
					{
						product	= new Product();
						product.setProductId( rs.getInt( "Product_Id" ) );
						product.setProcessStepId( rs.getInt( "ProcessStep_id" ) );
						partNumber = rs.getString( "Part_Number" );
						product.setPartNumber( partNumber );
						product.setProcessType( rs.getString( "PS_Type" ) );
						product.setBaseProcessType( rs.getString( "PS_Type_Name" ) );
						allProductList.add( product );
					}

					baseProductsMap.put( baseProductWip.getPartNumber(), allProductList );

					if ( rs != null ){	try { rs.close(); } catch (SQLException ignore) {}	}
					if ( pstmt != null ){	try { pstmt.close(); } catch (SQLException ignore) {}}
				}

				if( baseProductList != null && baseProductList.isEmpty() == false )
				{
					for( int i = 0; i < baseProductList.size(); i++ )
					{
						baseProductWip = ( WipFlushByDate )baseProductList.get( i );
						productsMap = baseProductWip.getChildMap();
						if( productsMap == null )
						{
							productsMap = new LinkedHashMap();
						}

						if( baseProductsMap.containsKey( baseProductWip.getPartNumber() ) )
						{
							allProductList = (ArrayList)baseProductsMap.get( baseProductWip.getPartNumber() );

							for( int j = 0; j < allProductList.size(); j++ )
							{
								product = (Product)allProductList.get( j );
								partNumber = product.getPartNumber();

								if( productsMap.containsKey( partNumber ) )
								{
									productWip = ( WipFlushByDate )productsMap.get( partNumber );
									productList = productWip.getProducts();
									productList.add( product );
								}
								else
								{
									productWip = new WipFlushByDate();
									productWip.setPartNumber( partNumber );
									productList = new ArrayList();
									productList.add( product );
									productWip.setProducts( productList );
									productsMap.put( partNumber, productWip );
									baseProductWip.setChildMap( productsMap );
								}
							}
						}
					}
				}
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Base products could not be fetched" );
		}
		finally
		{
			if ( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if ( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

		return baseProductList;
	}

	/**
	 * @param dbTransaction
     * @param baseProductList
     * @param flag
     * @param holdLot
     * @param wwStartDate
     * @param fgProcessStepIds
     */
	private static void fetchVendorLotInformationByWeek(DBTransaction dbTransaction, ArrayList baseProductList, int flag, String holdLot, Date wwStartDate, int[] fgProcessStepIds) throws ScalarException
	{
		WipFlushByDate baseProductWip = null;
		WipFlushByDate productWip 	= null;
		LinkedHashMap productsMap 	= null;
		Iterator productsIterator = null;
        HashMap shippableVendorWipMap = null;
        LinkedHashMap vendorMap     = null;
        if( baseProductList != null && baseProductList.isEmpty() == false )
		{
            if ( WIP_SHIPPABLE_UNITS == flag )
            {
                int holdType = 1; //ALL
                try{ holdType = Integer.parseInt ( holdLot ); } catch( NumberFormatException e ){ holdType = 1; }
                shippableVendorWipMap = getShippableVendorWipMapByWeek( dbTransaction, holdType, wwStartDate, fgProcessStepIds);
            }
            for( int i = 0; i < baseProductList.size(); i++ )
			{
				baseProductWip 	= ( WipFlushByDate ) baseProductList.get( i );
				productsMap 	= baseProductWip.getChildMap();
				if( productsMap != null && productsMap.isEmpty() == false )
				{
					productsIterator = productsMap.keySet().iterator();
					while( productsIterator.hasNext() )
					{
						productWip = (WipFlushByDate)productsMap.get( productsIterator.next() );
                        if( WIP_SHIPPABLE_UNITS == flag )
				        {
                            vendorMap = (LinkedHashMap)shippableVendorWipMap.get ( productWip.getPartNumber() );
                            if ( vendorMap == null )
                            {
                                vendorMap = new LinkedHashMap();
                            }
                            productWip.setChildMap(vendorMap);
                        }
                        else
                        {
                            fetchDataByWeek( dbTransaction, productWip, flag, holdLot, wwStartDate);
                        }
                    }
				}
			}
		}
	}

	/**
	 * @param dbTransaction
     * @param productWip
     * @param flag
     * @param wwStartDate
     * @param holdLot
     */
	private static void fetchDataByWeek(DBTransaction dbTransaction, WipFlushByDate productWip, int flag, String holdLot, Date wwStartDate) throws ScalarException
	{
		ArrayList productList 	= null;
		Product product 		= null;
		int processStepId 		= 0;
		LinkedHashMap childMap 		= new LinkedHashMap();

		productWip.setChildMap( childMap );
		productList = productWip.getProducts();

		if( productList != null && productList.isEmpty() == false )
		{
			for( int i = 0; i < productList.size(); i++ )
			{
				product = (Product)productList.get( i );
				processStepId = product.getProcessStepId();

				setShippableLotDataByWeek( dbTransaction, processStepId, childMap, flag, holdLot, wwStartDate);
			}
		}
	}


	/**
	 * @param dbTransaction
     * @param processStepId
     * @param vendorMap
     * @param flag
     * @param wwStartDate
     */
	private static void setShippableLotDataByWeek(DBTransaction dbTransaction, int processStepId, HashMap vendorMap, int flag, String holdLot, Date wwStartDate) throws ScalarException
	{
		WipFlushByDate vendorWip = null;
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;

		ArrayList wipList 		= new ArrayList();
		WipQuantity wip 		= null;

		LinkedHashMap lotMap			= null;
		WipFlushByDate lotWip 	= null;
		String vendorName		= null;
		String lotNumber 		= null;
		Date fg_date			= null;
		int fgQty 				= 0;
		int lotId				= 0;

        try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            if( processStepId != 0 )
			{
				/*
				 *  The Join Condition here is to select only the Max Fg_date Lot details,
				 *  The query before the Join gives same Lots with different Fg_date also as new record
				 */
				if( holdLot == null || holdLot.equalsIgnoreCase( "1" ) )
				{
					if( WIP_SHIPPABLE_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out, FG_PROCESSSTEP_ID from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and FG_PROCESSSTEP_ID = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and FG_PROCESSSTEP_ID = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) as FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_ALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated as FG_Qty, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? ) as A RIGHT outer join ( select Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Location where Wip_Master.Location_ID = Location.Location_ID and processStep_Id = ? group by lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
				}
				else if( holdLot.equalsIgnoreCase( "2" ) )
				{
					if( WIP_SHIPPABLE_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where	Wip_Master.Lot_id = Lot_table.Lot_Id and Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and Lot_Table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, lot_table, Location where Wip_Master.Lot_Id = Lot_table.Lot_Id and Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and Lot_Table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' group by Wip_Master.lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) as FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_Table, Location (NOLOCK) where Wip_Master.Lot_Id = Lot_Table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Lot_Table, Location where Wip_Master.Lot_Id = Lot_Table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' group by Wip_Master.lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_ALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated as FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_Table, Location (NOLOCK) where Wip_Master.Lot_id = Lot_table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Lot_Table, Location where Wip_Master.Lot_id = Lot_table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' group by Wip_Master.lot_id ) as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
				}
				else
				{
					if( WIP_SHIPPABLE_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where	Wip_Master.Lot_id = Lot_table.Lot_Id and Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, lot_table, Location where Wip_Master.Lot_Id = Lot_table.Lot_Id and Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) group by Wip_Master.lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) as FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_Table, Location (NOLOCK) where Wip_Master.Lot_Id = Lot_Table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Lot_Table, Location where Wip_Master.Lot_Id = Lot_Table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) group by Wip_Master.lot_id )as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
					else if( WIP_ALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select * from ( ( select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated as FG_Qty, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_Table, Location (NOLOCK) where Wip_Master.Lot_id = Lot_table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) ) as A RIGHT outer join ( select Wip_Master.Lot_ID, max(fg_date) as Expected_Date_Out from Wip_Master, Lot_Table, Location where Wip_Master.Lot_id = Lot_table.Lot_Id AND Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) group by Wip_Master.lot_id ) as B on A.Expected_Date_Out = B.Expected_Date_Out and A.Lot_Id = B.Lot_ID )" );
					}
				}
				pstmt.setInt( 1, processStepId );
				pstmt.setInt( 2, processStepId );
				rs 	  = pstmt.executeQuery();
				while( rs.next() )
				{
					vendorName 	= null;
					lotId 		= rs.getInt( "Lot_Id" );
					lotNumber 	= rs.getString( "Lot_Number" );
					vendorName 	= rs.getString( "L_Vendor_Name" );
					fgQty 		= rs.getInt( "FG_Qty" );
					fg_date 	= rs.getDate( "Expected_Date_Out" );

					if( fg_date == null )
					{
						fg_date = new Date();
					}

					if( fgQty > 0 )
					{
						wip = new WipQuantity( lotId, lotNumber, vendorName, fgQty, fg_date );
						wipList.add( wip );
					}
				}

				if( wipList != null && wipList.isEmpty() == false )
				{
					for( int i = 0; i < wipList.size(); i++ )
					{
						wip = (WipQuantity)wipList.get( i );

						lotId 		= wip.getLotId();
						lotNumber 	= wip.getLotNumber();
						vendorName 	= wip.getVendorName();
						fgQty 		= wip.getFgQty();
						fg_date 	= wip.getFg_date();

						if( vendorMap.containsKey( vendorName ) )
						{
							vendorWip = (WipFlushByDate)vendorMap.get( vendorName );
							lotMap = vendorWip.getChildMap();

							// Check wether any lot Data is present
							if( lotMap == null )
							{
								lotMap = new LinkedHashMap();
								vendorWip.setChildMap( lotMap );
							}

							if ( lotMap.containsKey( lotNumber ) )
							{
								lotWip = (WipFlushByDate)lotMap.get( lotNumber );
								setWipQtyByDate( lotWip, fg_date, fgQty, wwStartDate);
							}
							else
							{
								lotWip = new WipFlushByDate();
								lotWip.setLotNumber( lotNumber );
								lotMap.put( lotNumber, lotWip );
								setWipQtyByDate( lotWip, fg_date, fgQty, wwStartDate);
							}
						}
						else
						{
							vendorWip = new WipFlushByDate();
							vendorWip.setVendor( vendorName );
							vendorMap.put( vendorName, vendorWip );

							lotMap = new LinkedHashMap();
							vendorWip.setChildMap( lotMap );

							lotWip = new WipFlushByDate();
							lotWip.setLotNumber( lotNumber );
							lotMap.put( lotNumber, lotWip );
							setWipQtyByDate( lotWip, fg_date, fgQty, wwStartDate);
						}
					}
				}
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Lot Information could not be fetched" );
		}
		finally
		{
			if ( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if ( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}


    /**
     * @param baseProductList
     * @return
     */
    private static ArrayList fetchVendorMapByWeek( ArrayList baseProductList )
    {
		ArrayList vendorList = new ArrayList();
		HashMap finalVendorMap = new HashMap();
		LinkedHashMap finalBaseProductMap = null;
		LinkedHashMap finalProductMap = null;
		LinkedHashMap finalLotMap = null;

		WipFlushByDate finalBaseproduct = null;
		WipFlushByDate finalProduct 	 = null;
		WipFlushByDate finalVendor 	 = null;

		WipFlushByDate baseproduct = null;
		WipFlushByDate product 	 = null;
		WipFlushByDate vendor 	 = null;

		HashMap products 	= null;
		HashMap vendorMap 	= null;
		LinkedHashMap lotMap 		= null;

		Iterator productIt  = null;
		Iterator vendorIt 	= null;

		if( baseProductList != null && baseProductList.isEmpty() == false )
		{
			for( int i = 0; i < baseProductList.size(); i++ )
			{
				baseproduct = (WipFlushByDate)baseProductList.get( i );
				products 	= baseproduct.getChildMap();

				if( products != null )
				{
					productIt = products.keySet().iterator();
					while( productIt.hasNext() )
					{
						product = (WipFlushByDate)products.get( productIt.next() );
						vendorMap = product.getChildMap();
						if( vendorMap != null )
						{
							vendorIt = vendorMap.keySet().iterator();
							while( vendorIt.hasNext() )
							{
								vendor = (WipFlushByDate)vendorMap.get( vendorIt.next() );
								lotMap = vendor.getChildMap();
								finalBaseproduct = null;
								finalProduct = null;

								if( finalVendorMap.containsKey( vendor.getVendor() ) )
								{
									finalVendor = (WipFlushByDate)finalVendorMap.get( vendor.getVendor() );
									finalBaseProductMap = finalVendor.getChildMap();

									if( finalBaseProductMap != null && finalBaseProductMap.containsKey( baseproduct.getPartNumber() ) )
									{
										finalBaseproduct = (WipFlushByDate)finalBaseProductMap.get( baseproduct.getPartNumber() );
										finalProductMap = finalBaseproduct.getChildMap();

										if( finalProductMap != null && finalProductMap.containsKey( product.getPartNumber() ) )
										{
											finalProduct = (WipFlushByDate)finalProductMap.get( product.getPartNumber() );
											finalLotMap  = finalProduct.getChildMap();
											addLotsByLotNumber( finalLotMap, lotMap );
											finalProduct.setChildMap( finalLotMap );
										}
										else
										{
											if( finalProductMap == null )
											{
												finalProductMap = new LinkedHashMap();
											}

											finalProduct = new WipFlushByDate();
											finalProduct.setPartNumber( product.getPartNumber() );
											finalProductMap.put( product.getPartNumber(), finalProduct );
											finalBaseproduct.setChildMap( finalProductMap );
											finalProduct.setChildMap( lotMap );
										}
									}
									else
									{
										if( finalBaseProductMap == null )
										{
											finalBaseProductMap = new LinkedHashMap();
										}

										finalBaseproduct = new WipFlushByDate();
										finalBaseproduct.setPartNumber( baseproduct.getPartNumber() );
											finalBaseProductMap.put( baseproduct.getPartNumber(), finalBaseproduct );
										finalVendor.setChildMap( finalBaseProductMap );

										finalProductMap = new LinkedHashMap();
										finalProduct = new WipFlushByDate();
										finalProduct.setPartNumber( product.getPartNumber() );
										finalProduct.setChildMap( lotMap );
										finalProductMap.put( product.getPartNumber(), finalProduct );
										finalBaseproduct.setChildMap( finalProductMap );
									}
								}
								else
								{
									finalVendor = new WipFlushByDate();
									finalVendor.setVendor( vendor.getVendor() );
									finalVendorMap.put( vendor.getVendor(), finalVendor );

									finalBaseProductMap = new LinkedHashMap();
									finalBaseproduct = new WipFlushByDate();
									finalBaseproduct.setPartNumber( baseproduct.getPartNumber() );
									finalBaseProductMap.put( baseproduct.getPartNumber(), finalBaseproduct );
									finalVendor.setChildMap( finalBaseProductMap );

									finalProductMap = new LinkedHashMap();
									finalProduct = new WipFlushByDate();
									finalProduct.setPartNumber( product.getPartNumber() );
									finalProduct.setChildMap( lotMap );
									finalProductMap.put( product.getPartNumber(), finalProduct );
									finalBaseproduct.setChildMap( finalProductMap );
								}
							}
						}
					}
				}
			}
		}

		Iterator finalVendorIterator = null;

		if( finalVendorMap != null)
		{
			finalVendorIterator = finalVendorMap.keySet().iterator();
			while( finalVendorIterator.hasNext() )
			{
				vendorList.add( finalVendorMap.get( finalVendorIterator.next() ) );
			}
		}

		return vendorList;
    }

    /**
     * @param baseProductList
     */
    private static void setRemoveProductsWithoutQtyByWeek( ArrayList baseProductList )
    {
		WipFlushByDate baseproduct = null;
		WipFlushByDate product 	 = null;

		HashMap products = null;

		if( baseProductList != null && baseProductList.isEmpty() == false )
		{
			for( int i = baseProductList.size() - 1; i >= 0 ; i-- )
			{
				baseproduct = (WipFlushByDate)baseProductList.get(i);
				if( baseproduct.getTotal() == 0 )
				{
					baseProductList.remove( baseproduct );
				}
				else
				{
					products = baseproduct.getChildMap();

					if( products != null && products.isEmpty() == false  )
					{
						Object productKeys[] = products.keySet().toArray();

						for( int j = productKeys.length - 1; j >= 0; j--  )
						{
							product = (WipFlushByDate)products.get( productKeys[j] );
							if( product.getTotal() == 0 )
							{
								products.remove( productKeys[j] );
							}
						}
					}
					else
					{
						baseProductList.remove( baseproduct );
					}
				}
			}
		}
    }

    /**
     * @param baseProductList
     */
    private static void setQuantityInAllByWeek( ArrayList baseProductList )
    {
		WipFlushByDate baseproduct = null;
		WipFlushByDate product 	 = null;
		WipFlushByDate vendor 	 = null;
		WipFlushByDate lot 		 = null;

		HashMap products	= null;
		HashMap vendorMap	= null;
		HashMap lotMap		= null;

		Iterator productIt  = null;
		Iterator vendorIt 	= null;
		Iterator lotIt 	  	= null;

		if( baseProductList != null && baseProductList.isEmpty() == false )
		{
			for( int i = baseProductList.size() - 1; i >= 0 ; i-- )
			{
				baseproduct = (WipFlushByDate)baseProductList.get(i);
				products = baseproduct.getChildMap();

				if( products != null )
				{
					productIt = products.keySet().iterator();
					while( productIt.hasNext() )
					{
						product = (WipFlushByDate)products.get( productIt.next() );
						vendorMap = product.getChildMap();
						if( vendorMap != null )
						{
							vendorIt = vendorMap.keySet().iterator();
							while( vendorIt.hasNext() )
							{
								vendor = (WipFlushByDate)vendorMap.get( vendorIt.next() );
								lotMap = vendor.getChildMap();

								if( lotMap != null && lotMap.isEmpty() == false )
								{
									lotIt = lotMap.keySet().iterator();
									while( lotIt.hasNext() )
									{
										lot = (WipFlushByDate)lotMap.get( lotIt.next() );
										addWipByWeek( vendor, lot );
									}
								}
								addWipByWeek( product, vendor );
							}
						}
						addWipByWeek( baseproduct, product );
					}
				}
			}
		}
	}

	/**
	 * @param mainWip
	 * @param childWip
	 */
	private static void addWipByWeek( WipFlushByDate mainWip, WipFlushByDate childWip )
	{
		mainWip.setWeek1Qty( mainWip.getWeek1Qty() + childWip.getWeek1Qty() );
		mainWip.setWeek2Qty( mainWip.getWeek2Qty() + childWip.getWeek2Qty() );
		mainWip.setWeek3Qty( mainWip.getWeek3Qty() + childWip.getWeek3Qty() );
		mainWip.setWeek4Qty( mainWip.getWeek4Qty() + childWip.getWeek4Qty() );
		mainWip.setWeek5Qty( mainWip.getWeek5Qty() + childWip.getWeek5Qty() );
		mainWip.setWeek6Qty( mainWip.getWeek6Qty() + childWip.getWeek6Qty() );
		mainWip.setWeek7Qty( mainWip.getWeek7Qty() + childWip.getWeek7Qty() );
		mainWip.setWeek8Qty( mainWip.getWeek8Qty() + childWip.getWeek8Qty() );
		mainWip.setWeek9Qty( mainWip.getWeek9Qty() + childWip.getWeek9Qty() );
		mainWip.setWeek10Qty( mainWip.getWeek10Qty() + childWip.getWeek10Qty() );
		mainWip.setWeek11Qty( mainWip.getWeek11Qty() + childWip.getWeek11Qty() );
		mainWip.setWeek12Qty( mainWip.getWeek12Qty() + childWip.getWeek12Qty() );
		mainWip.setWeek13Qty( mainWip.getWeek13Qty() + childWip.getWeek13Qty() );
		mainWip.setWeek14Qty( mainWip.getWeek14Qty() + childWip.getWeek14Qty() );
		mainWip.setWeek15Qty( mainWip.getWeek15Qty() + childWip.getWeek15Qty() );
		mainWip.setWeek16Qty( mainWip.getWeek16Qty() + childWip.getWeek16Qty() );
		mainWip.setWeek17Qty( mainWip.getWeek17Qty() + childWip.getWeek17Qty() );
		mainWip.setWeek18Qty( mainWip.getWeek18Qty() + childWip.getWeek18Qty() );
		mainWip.setWeek19Qty( mainWip.getWeek19Qty() + childWip.getWeek19Qty() );
		mainWip.setWeek20Qty( mainWip.getWeek20Qty() + childWip.getWeek20Qty() );
		mainWip.setWeek21Qty( mainWip.getWeek21Qty() + childWip.getWeek21Qty() );
		mainWip.setWeek22Qty( mainWip.getWeek22Qty() + childWip.getWeek22Qty() );
		mainWip.setWeek23Qty( mainWip.getWeek23Qty() + childWip.getWeek23Qty() );
		mainWip.setWeek24Qty( mainWip.getWeek24Qty() + childWip.getWeek24Qty() );
		mainWip.setWeek25Qty( mainWip.getWeek25Qty() + childWip.getWeek25Qty() );
		mainWip.setWeek26Qty( mainWip.getWeek26Qty() + childWip.getWeek26Qty() );
		mainWip.setTotal( mainWip.getTotal() + childWip.getTotal() );
	}



    public static ArrayList getWipInventoryByVendor(int[] baseProductIds, int vendorId, int flag, String holdLot, int[] fgProcessStepIds) throws ScalarException
	{
		ArrayList baseProductList = new ArrayList();
		DBTransaction dbTransaction = null;
		WipFlush baseProductWip = null;
		WipFlush vendorProductWip = null;
		//HashMap vendorMap = new HashMap();
		HashMap baseProductMap = new HashMap();
		Iterator baseProductIterator = null;

		ArrayList vendorProductList = new ArrayList();

		try
		{
			// Start transaction
			//dbTransaction = DBTransaction.getInstance();
			//dbTransaction.begin();

			// Get all the Base Products into the HashMap
			baseProductList = fetchBaseProductList( null /*dbTransaction*/, baseProductIds, vendorId );

			// Set the Vendors Map into ChildMap the BaseProductList
			fetchVendorLotInformation( dbTransaction, baseProductList, flag, holdLot, baseProductIds, fgProcessStepIds);

			// Make Vendor HashMap
			vendorProductList = fetchVendorMap( baseProductList );

			// here transaction is rolled back only to close the Transaction instance
			/*if( dbTransaction != null )
			{
				dbTransaction.rollback();
				dbTransaction = null;
			}*/

			// Set the Quantities at all WipFlush Objects
			setQuantityInAll( vendorProductList, holdLot );

			// Remove all products where the WIP Quantity does not exist
			setRemoveProductsWithoutQty( vendorProductList );

			// Sort the results from FAB to FG
            // Commented as we r handling it in fetchBaseProductList() method
			/*if( vendorProductList != null && vendorProductList.isEmpty() == false )
			{
				for ( int i = 0; i < vendorProductList.size(); i++ )
				{
					vendorProductWip = (WipFlush)vendorProductList.get( i );
					baseProductMap 	 = vendorProductWip.getChildMap();

					if( baseProductMap != null )
					{
						baseProductIterator = baseProductMap.keySet().iterator();

						while( baseProductIterator.hasNext() )
						{
							baseProductWip = (WipFlush) baseProductMap.get( baseProductIterator.next() );
							sortProductsByStage( baseProductWip );
						}
					}
				}
			}*/

			// To Show results
			// showValues( vendorProductList );
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Wip information could not be fetched" );
		}
		finally
		{
			// here transaction is rolled back only to close the Transaction instance
			//if( dbTransaction != null )
			//{
			//	dbTransaction.rollback();
			//}
		}

		return vendorProductList;
	}

	public static ArrayList getWipInventoryByProduct(int[] baseProductIds, int vendorId, int flag, String holdLot, int[] fgProcessStepIds) throws ScalarException
	{
		ArrayList baseProductList = new ArrayList();
		DBTransaction dbTransaction = null;

		try
		{
			// Start transaction
			//dbTransaction = DBTransaction.getInstance();
			//dbTransaction.begin();

			// Get all the Base Products into the HashMap
			baseProductList = fetchBaseProductList( dbTransaction, baseProductIds, vendorId );

            // Set the Vendors Map into ChildMap the BaseProductList
			fetchVendorLotInformation( dbTransaction, baseProductList, flag, holdLot, baseProductIds, fgProcessStepIds);

            // here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
			}

			// Set the Quantities at all WipFlush Objects
			setQuantityInAll( baseProductList, holdLot );

			// Remove all products where the WIP Quantity does not exist
			setRemoveProductsWithoutQty( baseProductList );
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Wip information could not be fetched" );
		}
		finally
		{
			// here transaction is rolled back only to close the Transaction instance
			if( dbTransaction != null )
			{
				dbTransaction.rollback();
			}
	   	}

		return baseProductList;
	}


	/**
     * @param baseProductWip
     */
    private static void sortProductsByStage( WipFlush baseProductWip )
    {
    	LinkedHashMap sortedProductsMap = new LinkedHashMap();
		HashMap productsMap = baseProductWip.getChildMap();
		Iterator productIterator = null;
		WipFlush productWip = null;
		Object obj = null;

        // For Fab Qty
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getFabQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For Wafer Store
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getWaferStoreQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For Sort Quantity
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getSortQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For Die Bank
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getDieBankQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For Assembly
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getAssemblyQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For Test
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getTestQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For ClassStore
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getClassStoreQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For Mark And Pack
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getMarkPackQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For Finished Goods
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getFgQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

        // For Backlog Qty
		if( productsMap != null && productsMap.isEmpty() == false )
		{
			productIterator = productsMap.keySet().iterator();

			while( productIterator.hasNext() )
			{
				obj = productIterator.next();
				productWip = ( WipFlush )productsMap.get( obj );
				if( productWip.getBacklogQty() > 0 )
				{
					sortedProductsMap.put( obj, productWip );
					productsMap.remove( obj );
					productIterator = productsMap.keySet().iterator();
				}
			}
		}

		baseProductWip.setChildMap( sortedProductsMap );
		return ;
    }

    /**
	 * @param baseProductIds
	 * @param vendorId
	 * @return
	 */
	private static ArrayList fetchBaseProductList( DBTransaction dbTransaction, int[] baseProductIds, int vendorId) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;

		ArrayList baseProductList = null; // This is the Main ArrayList having all the Base Products Details
		WipFlush baseProductWip   = null; // This is the WipFlush Object for the Base Product

		LinkedHashMap productsMap 	= null;   // This is the Sub Map in Base Product Object to carry all the Products under it
		ArrayList productList 	= null;   // Since there can be many products with the same name. This carries all the products with the same name
		Product product 		= null;   // This is the Object to store Information about each Product
		String partNumber 		= null;
		WipFlush productWip 	= null;	  // This is the Object to store the information of Products

		ArrayList allProductList = null;
		HashMap baseProductsMap = new HashMap();
        StringBuffer baseProducts = new StringBuffer();
        if ( baseProductIds != null )
        {
            for ( int i=0; i < baseProductIds.length; i++ )
            {
                if ( baseProductIds[i] == 0 )
                {
                    baseProducts = null;
                    break;
                }
                else
                {
                    baseProducts.append( baseProductIds[i] ).append( "," );
                }
            }
            if ( baseProducts != null )
            {
                baseProducts.deleteCharAt( baseProducts.lastIndexOf(",") );
            }
        }
        else
        {
            baseProducts = null;
        }
        try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			if( baseProducts == null )
			{
				pstmt = conn.prepareStatement( "Select Product_Id, Part_Number from product (NOLOCK) where P_Parent_Product_Id is null and P_Type is null ORDER BY Part_Number" );
			}
			else
			{
				pstmt = conn.prepareStatement( "Select Product_Id, Part_Number from product (NOLOCK) where  Product_Id IN (" + baseProducts.toString() + ") ORDER BY Part_Number" );
				//pstmt.setInt( 1, baseProductId );
			}

			rs 	  = pstmt.executeQuery();

			while( rs.next() )
			{
				if( baseProductList == null )
				{
					baseProductList = new ArrayList();
				}

				baseProductWip = new WipFlush();

				baseProductWip.setProductId( rs.getInt( "Product_Id" ) );
				baseProductWip.setPartNumber( rs.getString( "Part_Number" ) );
				baseProductList.add( baseProductWip );
			}

            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) { }
            }
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

			if( baseProductList != null && baseProductList.isEmpty() == false )
			{
				for( int i = 0; i < baseProductList.size(); i++ )
				{
					baseProductWip = ( WipFlush )baseProductList.get( i );
					allProductList = null;
					allProductList = new ArrayList();

					if( vendorId == 0 )
					{
						pstmt = conn.prepareStatement( "select Product_Id, Product.ProcessStep_id, Part_Number, PS_Type, PS_Type_Name, " +
                                " (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = Process_Step.PS_Type)  AS PS_Type_Name_Order_No from Product(NOLOCK), Process_Step (NOLOCK) where Product.ProcessStep_id = Process_Step.ProcessStep_id and P_Parent_Product_Id = ? order by PS_Type_Name_Order_No, Part_Number" );
						pstmt.setInt( 1, baseProductWip.getProductId() );
					}
					else
					{
						pstmt = conn.prepareStatement( "select Product_Id, Product.ProcessStep_id, Part_Number, PS_Type, PS_Type_Name, " +
                                " (SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = Process_Step.PS_Type) AS PS_Type_Name_Order_No from Product(NOLOCK), Process_Step(NOLOCK), PROCESS_STEP_LOCATION_TABLE(NOLOCK), LOCATION (NOLOCK) where Product.ProcessStep_id = Process_Step.ProcessStep_id and Process_Step.ProcessStep_id = PROCESS_STEP_LOCATION_TABLE.ProcessStep_id and PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID and P_Parent_Product_Id = ? AND VENDOR_ID = ? order by PS_Type_Name_Order_No, Part_Number" );
						pstmt.setInt( 1, baseProductWip.getProductId() );
						pstmt.setInt( 2, vendorId );
					}
					rs = pstmt.executeQuery();

					while( rs.next() )
					{
						product	= new Product();
						product.setProductId( rs.getInt( "Product_Id" ) );
						product.setProcessStepId( rs.getInt( "ProcessStep_id" ) );
						partNumber = rs.getString( "Part_Number" );
						product.setPartNumber( partNumber );
						product.setProcessType( rs.getString( "PS_Type" ) );
						product.setBaseProcessType( rs.getString( "PS_Type_Name" ) );
						allProductList.add( product );
					}
					baseProductsMap.put( baseProductWip.getPartNumber(), allProductList );
					if ( rs != null ){	try { rs.close(); } catch (SQLException ignore) {}	}
					if ( pstmt != null ){	try { pstmt.close(); } catch (SQLException ignore) {}}
				}

				for( int i = 0; i < baseProductList.size(); i++ )
				{
					baseProductWip = ( WipFlush )baseProductList.get( i );
					productsMap = baseProductWip.getChildMap();
					if( productsMap == null )
					{
						productsMap = new LinkedHashMap();
					}

					if( baseProductsMap.containsKey( baseProductWip.getPartNumber() ) )
					{
						allProductList = (ArrayList)baseProductsMap.get( baseProductWip.getPartNumber() );
						for( int j = 0; j < allProductList.size(); j++ )
						{
							product = (Product)allProductList.get( j );
							partNumber = product.getPartNumber();

							if( productsMap.containsKey( partNumber ) )
							{
								productWip = ( WipFlush )productsMap.get( partNumber );
								productList = productWip.getProducts();
								productList.add( product );
							}
							else
							{
								productWip = new WipFlush();
								productWip.setPartNumber( partNumber );
                                productWip.setProcess_Type( product.getProcessType() );
                                productWip.setProductId( product.getProductId() );
								productList = new ArrayList();
								productList.add( product );
								productWip.setProducts( productList );
								productsMap.put( partNumber, productWip );
								baseProductWip.setChildMap( productsMap );
							}
						}
					}
				}
				baseProductsMap = null;
				allProductList = null;
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Base products could not be fetched" );
		}
		finally
		{
			if ( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if ( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

		return baseProductList;
	}

	/**
	 * @param baseProductList
     * @param fgProcessStepIds
	 */
	private static void fetchVendorLotInformation(DBTransaction dbTransaction, ArrayList baseProductList, int flag, String holdLot, int[] baseProductIds, int[] fgProcessStepIds) throws ScalarException
	{
        WipFlush baseProductWip = null;
		WipFlush productWip 	= null;
		HashMap productsMap 	= null;
		Iterator productsIterator = null;
        HashMap productVendorWipMap = null;
		if( baseProductList != null && baseProductList.isEmpty() == false )
		{
            if ( WIP_INVENTORY_UNITS == flag )
            {
                int holdType = 1; //ALL
                try{ holdType = Integer.parseInt ( holdLot ); } catch( NumberFormatException e ){ holdType = 1; }
                productVendorWipMap = getProductVendorWipMap( dbTransaction, holdType, baseProductIds, fgProcessStepIds);
            }
            LinkedHashMap vendorMap = null;
            for( int i = 0; i < baseProductList.size(); i++ )
			{
				baseProductWip 	= ( WipFlush ) baseProductList.get( i );
				productsMap 	= baseProductWip.getChildMap();
				if( productsMap != null && productsMap.isEmpty() == false )
				{
					productsIterator = productsMap.keySet().iterator();
					while( productsIterator.hasNext() )
					{
						productWip = (WipFlush)productsMap.get( productsIterator.next() );
                        if( WIP_INVENTORY_UNITS == flag )
				        {
                            vendorMap = (LinkedHashMap)productVendorWipMap.get ( productWip.getPartNumber() );
                            if ( vendorMap == null )
                            {
                                vendorMap = new LinkedHashMap();
                            }
                            productWip.setChildMap(vendorMap);
                        }
                        else
                        {
                            fetchData( dbTransaction, productWip, flag, holdLot );
                        }
                    }
				}
			}
		}
    }


	/**
	 * @param productWip
	 */
	private static void fetchData( DBTransaction dbTransaction, WipFlush productWip, int flag, String holdLot ) throws ScalarException
	{
		ArrayList productList 	= null;
		Product product 		= null;
		int processStepId 		= 0;
		String processType 		= null;
		LinkedHashMap childMap 	= new LinkedHashMap();

		productWip.setChildMap( childMap );
        productList = productWip.getProducts();
		if( productList != null && productList.isEmpty() == false )
		{
			for( int i = 0; i < productList.size(); i++ )
			{
				product = (Product)productList.get( i );
                processStepId = product.getProcessStepId();
				processType   = product.getBaseProcessType();

                if( WIP_SHIPPABLE_UNITS == flag || WIP_ALLOCATED_UNITS == flag || WIP_UNALLOCATED_UNITS == flag )
				{
					setShippableLotData( dbTransaction, processStepId, processType, childMap, flag, holdLot );
				}
			}
		}
    }


    /**
	 * @param dbTransaction
	 * @param processStepId
	 * @param processType
	 * @param vendorMap
	 */
	private static void setShippableLotData(DBTransaction dbTransaction, int processStepId, String processType, HashMap vendorMap, int flag, String holdLot ) throws ScalarException
	{
		WipFlush vendorWip 		= null;
		Connection conn 		= null;
		PreparedStatement pstmt	= null;
		ResultSet rs 			= null;

		LinkedHashMap lotMap	= null;
		WipFlush lotWip 		= null;
		LotView lotInfo			= null;
		String vendorName		= null;
		String lotNumber 		= null;
		int qtyIn 				= 0;
		int lotId				= 0;

		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			if( processStepId != 0 )
			{
				if( holdLot == null || holdLot.equalsIgnoreCase( "1" ) )
				{
					if( WIP_SHIPPABLE_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty as Qty_in, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? " );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) as Qty_in, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? " );
					}
					else if( WIP_ALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Lot_ID, LTrim( RTrim( Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated as Qty_in, Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Location (NOLOCK) where Wip_Master.Location_ID = Location.Location_ID and Wip_Master.processStep_Id = ? " );
					}
				}
				else if( holdLot.equalsIgnoreCase( "2" ) )
				{
					if( WIP_ALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated as Qty_in, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where Wip_Master.Lot_ID = Lot_table.Lot_ID and Wip_Master.Location_ID = Location.Location_ID and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' and Wip_Master.processStep_Id = ?" );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) as Qty_in, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where Wip_Master.Lot_ID = Lot_table.Lot_ID and Wip_Master.Location_ID = Location.Location_ID and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' and Wip_Master.processStep_Id = ? " );
					}
					else if ( WIP_SHIPPABLE_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty as Qty_in, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where Wip_Master.Lot_ID = Lot_table.Lot_ID and Wip_Master.Location_ID = Location.Location_ID and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'HOLD' and Wip_Master.processStep_Id = ? " );
					}
				}
				else
				{
					if( WIP_ALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, Qty_Allocated as Qty_in, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where Wip_Master.Lot_ID = Lot_table.Lot_ID and Wip_Master.Location_ID = Location.Location_ID and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) and Wip_Master.processStep_Id = ?" );
					}
					else if( WIP_UNALLOCATED_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, ( FG_Qty - Qty_Allocated ) as Qty_in, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where Wip_Master.Lot_ID = Lot_table.Lot_ID and Wip_Master.Location_ID = Location.Location_ID and ( Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold' ) and Wip_Master.processStep_Id = ? " );
					}
					else if ( WIP_SHIPPABLE_UNITS == flag )
					{
						pstmt = conn.prepareStatement( "select DISTINCT Wip_Master.Lot_ID, LTrim( RTrim( Wip_Master.Lot_Number ) ) as Lot_Number, L_Vendor_Name, FG_Qty as Qty_in, Wip_Master.Date_In as Date_in, Fg_Date as Expected_Date_Out from Wip_Master, Lot_table, Location (NOLOCK) where Wip_Master.Lot_ID = Lot_table.Lot_ID and Wip_Master.Location_ID = Location.Location_ID and (Lot_Table.Lot_Manual_Hold = 0 or Lot_Table.Lot_Current_Status = 'Hold') and Wip_Master.processStep_Id = ? " );
					}
				}

				pstmt.setInt( 1, processStepId );

				rs 	  = pstmt.executeQuery();
				while( rs.next() )
				{
					vendorName = null;
					lotInfo = new LotView();
					lotId = rs.getInt( "Lot_Id" );
					lotNumber = rs.getString( "Lot_Number" );
					vendorName = rs.getString( "L_Vendor_Name" );

					lotInfo.setLotID( lotId+"" );
					lotInfo.setLotNumber( lotNumber );
					lotInfo.setVendorLocationName( vendorName );
					qtyIn = rs.getInt( "Qty_in" );
					lotInfo.setQuantityIn( qtyIn );
					lotInfo.setDateIn( rs.getString( "Date_in" ) );
					lotInfo.setExpectedDateOut( rs.getString( "Expected_Date_Out" ) );

					if( qtyIn > 0 )
					{
						if( vendorMap.containsKey( vendorName ) )
						{
							vendorWip = (WipFlush)vendorMap.get( vendorName );
							lotMap = vendorWip.getChildMap();

							// Check wether any lot Data is present
							if( lotMap == null )
							{
								lotMap = new LinkedHashMap();
								vendorWip.setChildMap( lotMap );
							}

							if ( lotMap.containsKey( lotNumber ) )
							{
								lotWip = (WipFlush)lotMap.get( lotNumber );
								setWipQty( lotWip, processType, qtyIn );
							}
							else
							{
								lotWip = new WipFlush();
								lotWip.setLotNumber( lotNumber );
								lotMap.put( lotNumber, lotWip );
								setWipQty( lotWip, processType, qtyIn );
							}
						}
						else
						{
							vendorWip = new WipFlush();
							vendorWip.setVendor( vendorName );
							vendorMap.put( vendorName, vendorWip );

							lotMap = new LinkedHashMap();
							vendorWip.setChildMap( lotMap );

							lotWip = new WipFlush();
							lotWip.setLotNumber( lotNumber );
							lotMap.put( lotNumber, lotWip );
							setWipQty( lotWip, processType, qtyIn );
						}
					}
				}
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Lot Information could not be fetched" );
		}
		finally
		{
			if ( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if ( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}

	/**
	 * @param lotWip
	 * @param processType
	 * @param qtyIn
	 */
	private static void setWipQty( WipFlush lotWip, String processType, int qtyIn )
	{
		if( WIP_TYPE_FAB.equalsIgnoreCase( processType ) )
		{
			lotWip.setFabQty( lotWip.getFabQty() + qtyIn );
		}
        else if( LOCATION_TYPE_WAFER_BANK.equalsIgnoreCase( processType ) || LOCATION_TYPE_WAFER_STORE.equalsIgnoreCase( processType ) )
		{
			lotWip.setWaferStoreQty( lotWip.getWaferStoreQty() + qtyIn );
		}
        else if( WIP_TYPE_SORT.equalsIgnoreCase( processType ) )
		{
			lotWip.setSortQty( lotWip.getSortQty() + qtyIn );
		}
        else if( LOCATION_TYPE_FAB_OPTION.equalsIgnoreCase( processType ) )
		{
			lotWip.setFabOptionQty( lotWip.getFabOptionQty() + qtyIn );
		}
		else if( LOCATION_TYPE_DIE_BANK.equalsIgnoreCase( processType ) )
		{
			lotWip.setDieBankQty( lotWip.getDieBankQty() + qtyIn );
		}
		else if( WIP_TYPE_ASSEMBLY.equalsIgnoreCase( processType ) )
		{
			lotWip.setAssemblyQty( lotWip.getAssemblyQty() + qtyIn );
		}
        else if( LOCATION_TYPE_PRE_TEST.equalsIgnoreCase( processType ) )
		{
			lotWip.setPreTestQty( lotWip.getPreTestQty() + qtyIn );
		}
        else if( LOCATION_TYPE_FT.equalsIgnoreCase( processType ) ||  LOCATION_TYPE_QC.equalsIgnoreCase( processType ) ||  LOCATION_TYPE_BAKING.equalsIgnoreCase( processType )
                ||  LOCATION_TYPE_BALLSCAN.equalsIgnoreCase( processType ) ||  LOCATION_TYPE_RETEST.equalsIgnoreCase( processType ) )
		{
			lotWip.setTestQty( lotWip.getTestQty() + qtyIn );
		}
		else if( LOCATION_TYPE_FPO.equalsIgnoreCase( processType ) )
		{
			lotWip.setFpoQty( lotWip.getFpoQty() + qtyIn );
		}
        else if( LOCATION_TYPE_CLASS_STORE.equalsIgnoreCase( processType )  )
		{
			lotWip.setClassStoreQty( lotWip.getClassStoreQty() + qtyIn );
		}
		else if( LOCATION_TYPE_TAPE_REEL.equalsIgnoreCase( processType )  )
		{
			lotWip.setTapeReelQty( lotWip.getTapeReelQty() + qtyIn );
		}
		else if( LOCATION_TYPE_FG.equalsIgnoreCase( processType ) || LOCATION_TYPE_FINISHED_GOODS.equalsIgnoreCase( processType ))
		{
			lotWip.setFgQty( lotWip.getFgQty() + qtyIn );
		}

        lotWip.setTotal( lotWip.getFabQty() + lotWip.getWaferStoreQty() + lotWip.getSortQty() + lotWip.getFabOptionQty() + lotWip.getDieBankQty() + lotWip.getAssemblyQty() + lotWip.getPreTestQty() + lotWip.getTestQty() + lotWip.getFpoQty() + lotWip.getClassStoreQty() + lotWip.getTapeReelQty() + lotWip.getFgQty() );
	}


    /**
     * @param baseProductList
     */
    private static ArrayList fetchVendorMap( ArrayList baseProductList )
    {
    	ArrayList vendorList = new ArrayList();
        HashMap finalVendorMap = new HashMap();
		LinkedHashMap finalBaseProductMap = null;
		LinkedHashMap finalProductMap = null;
		LinkedHashMap finalLotMap = null;

		WipFlush finalBaseproduct = null;
		WipFlush finalProduct 	 = null;
		WipFlush finalVendor 	 = null;

		WipFlush baseproduct = null;
		WipFlush product 	 = null;
		WipFlush vendor 	 = null;

		HashMap products 	= null;
		HashMap vendorMap 	= null;
		LinkedHashMap lotMap 		= null;

		Iterator productIt  = null;
		Iterator vendorIt 	= null;

        if( baseProductList != null && baseProductList.isEmpty() == false )
        {
        	for( int i = 0; i < baseProductList.size(); i++ )
        	{
				baseproduct = (WipFlush)baseProductList.get( i );
				products 	= baseproduct.getChildMap();

				if( products != null )
				{
					productIt = products.keySet().iterator();
					while( productIt.hasNext() )
					{
						product = (WipFlush)products.get( productIt.next() );
						vendorMap = product.getChildMap();
						if( vendorMap != null )
						{
							vendorIt = vendorMap.keySet().iterator();
							while( vendorIt.hasNext() )
							{
								vendor = (WipFlush)vendorMap.get( vendorIt.next() );
								lotMap = vendor.getChildMap();
								finalBaseproduct = null;
								finalProduct = null;

								if( finalVendorMap.containsKey( vendor.getVendor() ) )
								{
									finalVendor = (WipFlush)finalVendorMap.get( vendor.getVendor() );
									finalBaseProductMap = finalVendor.getChildMap();

									if( finalBaseProductMap != null && finalBaseProductMap.containsKey( baseproduct.getPartNumber() ) )
									{
										finalBaseproduct = (WipFlush)finalBaseProductMap.get( baseproduct.getPartNumber() );
										finalProductMap = finalBaseproduct.getChildMap();

										if( finalProductMap != null && finalProductMap.containsKey( product.getPartNumber() ) )
										{
											finalProduct = (WipFlush)finalProductMap.get( product.getPartNumber() );
											finalLotMap  = finalProduct.getChildMap();
											addLotsByLotNumber( finalLotMap, lotMap );
											finalProduct.setChildMap( finalLotMap );
										}
										else
										{
											if( finalProductMap == null )
											{
												finalProductMap = new LinkedHashMap();
											}

											finalProduct = new WipFlush();
											finalProduct.setPartNumber( product.getPartNumber() );
											finalProductMap.put( product.getPartNumber(), finalProduct );
											finalBaseproduct.setChildMap( finalProductMap );
											finalProduct.setChildMap( lotMap );
										}
									}
									else
									{
										if( finalBaseProductMap == null )
										{
											finalBaseProductMap = new LinkedHashMap();
										}

										finalBaseproduct = new WipFlush();
										finalBaseproduct.setPartNumber( baseproduct.getPartNumber() );
                                        finalBaseProductMap.put( baseproduct.getPartNumber(), finalBaseproduct );
										finalVendor.setChildMap( finalBaseProductMap );

										finalProductMap = new LinkedHashMap();
										finalProduct = new WipFlush();
										finalProduct.setPartNumber( product.getPartNumber() );
										finalProduct.setChildMap( lotMap );
										finalProductMap.put( product.getPartNumber(), finalProduct );
										finalBaseproduct.setChildMap( finalProductMap );
									}
								}
								else
								{
									finalVendor = new WipFlush();
									finalVendor.setVendor( vendor.getVendor() );
									finalVendorMap.put( vendor.getVendor(), finalVendor );

									finalBaseProductMap = new LinkedHashMap();
									finalBaseproduct = new WipFlush();
									finalBaseproduct.setPartNumber( baseproduct.getPartNumber() );
									finalBaseProductMap.put( baseproduct.getPartNumber(), finalBaseproduct );
									finalVendor.setChildMap( finalBaseProductMap );

									finalProductMap = new LinkedHashMap();
									finalProduct = new WipFlush();
									finalProduct.setPartNumber( product.getPartNumber() );
									finalProduct.setChildMap( lotMap );
									finalProductMap.put( product.getPartNumber(), finalProduct );
									finalBaseproduct.setChildMap( finalProductMap );
								}
							}
						}
					}
				}
        	}
        }

        Iterator finalVendorIterator = null;

		if( finalVendorMap != null)
		{
			finalVendorIterator = finalVendorMap.keySet().iterator();
			while( finalVendorIterator.hasNext() )
			{
				vendorList.add( finalVendorMap.get( finalVendorIterator.next() ) );
			}
		}

		return vendorList;
    }

    /**
     * @param finalLotMap
     * @param lotMap
     */
    private static void addLotsByLotNumber(HashMap finalLotMap, HashMap lotMap)
    {
    	Iterator lotMapIterator = null;
    	String lotNumber = null;
    	WipFlush finalLotWip = null;
		WipFlush lotWip = null;

    	if( finalLotMap == null )
    	{
			finalLotMap = new HashMap();
    	}

    	if( lotMap != null && lotMap.isEmpty() == false )
    	{
			lotMapIterator = lotMap.keySet().iterator();

			while( lotMapIterator.hasNext() )
			{
				lotNumber = (String)lotMapIterator.next();
				if( finalLotMap.containsKey( lotNumber ) )
				{
					finalLotWip = (WipFlush) finalLotMap.get( lotNumber );
					lotWip 		= (WipFlush) lotMap.get( lotNumber );
					addWip( finalLotWip, lotWip );
				}
				else
				{
					finalLotMap.put( lotNumber, lotMap.get( lotNumber ) );
				}
			}
    	}
    }

    /**
     * @param baseProductList
     */
    private static void setRemoveProductsWithoutQty(ArrayList baseProductList)
    {
		WipFlush baseproduct = null;
		WipFlush product 	 = null;

		HashMap products = null;

		if( baseProductList != null && baseProductList.isEmpty() == false )
		{
			for( int i = baseProductList.size() - 1; i >= 0 ; i-- )
			{
				baseproduct = (WipFlush)baseProductList.get(i);
				if( baseproduct.getTotal() == 0 )
				{
					baseProductList.remove( baseproduct );
				}
				else
				{
					products = baseproduct.getChildMap();

					if( products != null && products.isEmpty() == false  )
					{
						Object productKeys[] = products.keySet().toArray();

						for( int j = productKeys.length - 1; j >= 0; j--  )
						{
							product = (WipFlush)products.get( productKeys[j] );
							if( product.getTotal() == 0 && product.getBacklogQty() == 0 )
							{
								products.remove( productKeys[j] );
							}
						}
					}
					else
					{
						baseProductList.remove( baseproduct );
					}
				}
			}
		}
    }

    /**
     * @param baseProductList
     */
    private static void setQuantityInAll( ArrayList baseProductList, String holdLot )
    {
		WipFlush baseproduct = null;
		WipFlush product 	 = null;
		WipFlush vendor 	 = null;
		WipFlush lot 		 = null;

		HashMap products = null;
		HashMap vendorMap 	  = null;
		HashMap lotMap 		  = null;

		Iterator productIt  = null;
		Iterator vendorIt 	= null;
		Iterator lotIt 	  	= null;
        HashMap backlogMap  = new HashMap();
        int backlogQty      = 0;
        /*if( holdLot != null && ( "1".equalsIgnoreCase( holdLot ) || "2".equalsIgnoreCase( holdLot ) ) )
        {
            try
            {
                backlogMap = loadBacklogQuantityMapByBFGI( null );
            }catch( ScalarException e ) { }
        }*/
        if( baseProductList != null && baseProductList.isEmpty() == false )
		{
			for( int i = baseProductList.size() - 1; i >= 0 ; i-- )
			{
				baseproduct = (WipFlush)baseProductList.get(i);
				products = baseproduct.getChildMap();

				if( products != null )
				{
					productIt = products.keySet().iterator();
					while( productIt.hasNext() )
					{
						product = (WipFlush)products.get( productIt.next() );

                            /*if( holdLot != null && ( "1".equalsIgnoreCase( holdLot ) || "2".equalsIgnoreCase( holdLot ) ) )
                            {
                                try
                                {
                                    backlogQty = Integer.parseInt ( (String)backlogMap.get ( product.getPartNumber() ) );
                                }
                                catch( NumberFormatException e )
                                {
                                    backlogQty = 0;
                                }
                                product.setBacklogQty( backlogQty );
                                product.setTotal( backlogQty );
                            }*/

						vendorMap = product.getChildMap();
						if( vendorMap != null )
						{
							vendorIt = vendorMap.keySet().iterator();
							while( vendorIt.hasNext() )
							{
								vendor = (WipFlush)vendorMap.get( vendorIt.next() );
								lotMap = vendor.getChildMap();

								if( lotMap != null && lotMap.isEmpty() == false )
								{
									lotIt = lotMap.keySet().iterator();
									while( lotIt.hasNext() )
									{
										lot = (WipFlush)lotMap.get( lotIt.next() );
										addWip( vendor, lot );
									}
								}
								addWip( product, vendor );
							}
						}
						addWip( baseproduct, product );
					}
				}
			}
		}
	}

	/**
	 * @param mainWip
	 * @param childWip
	 */
	private static void addWip( WipFlush mainWip, WipFlush childWip )
	{
		mainWip.setFabQty( mainWip.getFabQty() + childWip.getFabQty() );
        mainWip.setWaferStoreQty( mainWip.getWaferStoreQty() + childWip.getWaferStoreQty() );
        /*mainWip.setFabOptionQty( mainWip.getFabOptionQty() + childWip.getFabOptionQty() );*/
        mainWip.setSortQty( mainWip.getSortQty() + childWip.getSortQty() );
        mainWip.setDieBankQty( mainWip.getDieBankQty() + childWip.getDieBankQty() );
		mainWip.setAssemblyQty( mainWip.getAssemblyQty() + childWip.getAssemblyQty() );
        mainWip.setPreTestQty( mainWip.getPreTestQty() + childWip.getPreTestQty() );
		mainWip.setTestQty( mainWip.getTestQty() + childWip.getTestQty() );
        //mainWip.setFpoQty( mainWip.getFpoQty() + childWip.getFpoQty() );
        //mainWip.setClassStoreQty( mainWip.getClassStoreQty() + childWip.getClassStoreQty() );
        mainWip.setTapeReelQty( mainWip.getTapeReelQty() + childWip.getTapeReelQty() );
		mainWip.setFgQty( mainWip.getFgQty() + childWip.getFgQty() );
        mainWip.setBacklogQty( mainWip.getBacklogQty() + childWip.getBacklogQty() );
        mainWip.setTotal( mainWip.getTotal() + childWip.getTotal() );
	}


    /**
     * @param lotWip
     * @param fg_date
     * @param fgQty
     * @param wwStartDate
     */
    private static void setWipQtyByDate(WipFlushByDate lotWip, Date fg_date, int fgQty, Date wwStartDate)
    {
		long daysRemainingToExpectedDateOut = 0;
		int weekNumber = 0;

		Calendar nowCal = new GregorianCalendar();
        if ( wwStartDate != null )
        {
            nowCal.setTime(wwStartDate);
        }
        else
        {
            nowCal.setTime(new Date());
        }
        Date now = new GregorianCalendar(nowCal.get(Calendar.YEAR), nowCal.get(Calendar.MONTH), nowCal.get(Calendar.DATE)).getTime();

		daysRemainingToExpectedDateOut = ( ( fg_date.getTime() - now.getTime() )/( 1000*60*60*24 ) );

		if ( daysRemainingToExpectedDateOut < 0 )
		{
			weekNumber = 1;
		}
		else
		{
			weekNumber = (int)(daysRemainingToExpectedDateOut/7) + 1;
		}

		switch ( weekNumber )
		{
			case 1:
				lotWip.setWeek1Qty( lotWip.getWeek1Qty() + fgQty );
				break;
			case 2:
				lotWip.setWeek2Qty( lotWip.getWeek2Qty() + fgQty );
				break;
			case 3:
				lotWip.setWeek3Qty( lotWip.getWeek3Qty() + fgQty );
				break;
			case 4:
				lotWip.setWeek4Qty( lotWip.getWeek4Qty() + fgQty );
				break;
			case 5:
				lotWip.setWeek5Qty( lotWip.getWeek5Qty() + fgQty );
				break;
			case 6:
				lotWip.setWeek6Qty( lotWip.getWeek6Qty() + fgQty );
				break;
			case 7:
				lotWip.setWeek7Qty( lotWip.getWeek7Qty() + fgQty );
				break;
			case 8:
				lotWip.setWeek8Qty( lotWip.getWeek8Qty() + fgQty );
				break;
			case 9:
				lotWip.setWeek9Qty( lotWip.getWeek9Qty() + fgQty );
				break;
			case 10:
				lotWip.setWeek10Qty( lotWip.getWeek10Qty() + fgQty );
				break;
			case 11:
				lotWip.setWeek11Qty( lotWip.getWeek11Qty() + fgQty );
				break;
			case 12:
				lotWip.setWeek12Qty( lotWip.getWeek12Qty() + fgQty );
				break;
			case 13:
				lotWip.setWeek13Qty( lotWip.getWeek13Qty() + fgQty );
				break;
			case 14:
				lotWip.setWeek14Qty( lotWip.getWeek14Qty() + fgQty );
				break;
			case 15:
				lotWip.setWeek15Qty( lotWip.getWeek15Qty() + fgQty );
				break;
			case 16:
				lotWip.setWeek16Qty( lotWip.getWeek16Qty() + fgQty );
				break;
			case 17:
				lotWip.setWeek17Qty( lotWip.getWeek17Qty() + fgQty );
				break;
			case 18:
				lotWip.setWeek18Qty( lotWip.getWeek18Qty() + fgQty );
				break;
			case 19:
				lotWip.setWeek19Qty( lotWip.getWeek19Qty() + fgQty );
				break;
			case 20:
				lotWip.setWeek20Qty( lotWip.getWeek20Qty() + fgQty );
				break;
			case 21:
				lotWip.setWeek21Qty( lotWip.getWeek21Qty() + fgQty );
				break;
			case 22:
				lotWip.setWeek22Qty( lotWip.getWeek22Qty() + fgQty );
				break;
			case 23:
				lotWip.setWeek23Qty( lotWip.getWeek23Qty() + fgQty );
				break;
			case 24:
				lotWip.setWeek24Qty( lotWip.getWeek24Qty() + fgQty );
				break;
			case 25:
				lotWip.setWeek25Qty( lotWip.getWeek25Qty() + fgQty );
				break;
			default:
				lotWip.setWeek26Qty( lotWip.getWeek26Qty() + fgQty );

		}
		lotWip.setTotal( lotWip.getWeek1Qty() + lotWip.getWeek2Qty() + lotWip.getWeek3Qty() + lotWip.getWeek4Qty() + lotWip.getWeek5Qty() + lotWip.getWeek6Qty() + lotWip.getWeek7Qty() + lotWip.getWeek8Qty() + lotWip.getWeek9Qty()+ lotWip.getWeek10Qty() + lotWip.getWeek11Qty() + lotWip.getWeek12Qty() + lotWip.getWeek13Qty() + lotWip.getWeek14Qty() + lotWip.getWeek15Qty() + lotWip.getWeek16Qty() + lotWip.getWeek17Qty() + lotWip.getWeek18Qty() + lotWip.getWeek19Qty() + lotWip.getWeek20Qty() + lotWip.getWeek21Qty() + lotWip.getWeek22Qty() + lotWip.getWeek23Qty() + lotWip.getWeek24Qty() + lotWip.getWeek25Qty() + lotWip.getWeek26Qty() );
	}


    private static int loadBacklogQuantityByProductID( DBTransaction dbTransaction, String partNumber ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt	= null;
        ResultSet rs 			= null;
        int backlogQuantity     = 0;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if( partNumber != null && partNumber.trim().length() > 0 )
            {
                //pstmt = conn.prepareStatement( "SELECT ISNULL ( SUM( ISNULL( COOL.COOL_Qty, 0 ) ), 0 ) as BACKLOG_QTY FROM Customer_Open_Order_LineItem COOL INNER JOIN Sales_Order ON COOL.SalesOrder_ID = Sales_Order.SalesOrder_ID WHERE COOL.COOL_Status <> 'SHIPPED' AND Sales_Order.SO_Is_Sample = 1 AND COOL.Product_ID = ?" );
                //pstmt = conn.prepareStatement( "SELECT ISNULL ( SUM( ISNULL( COOL.COOL_Qty, 0 ) ), 0 ) as BACKLOG_QTY FROM Customer_Open_Order_LineItem COOL INNER JOIN Sales_Order ON COOL.SalesOrder_ID = Sales_Order.SalesOrder_ID WHERE COOL.COOL_Status <> 'SHIPPED' AND Sales_Order.SO_Is_Sample = 1 AND COOL.Product_ID IN ( SELECT P2.PRODUCT_ID FROM PRODUCT P1, PROCESS_STEP PS, FG_TO_FGI_MAP_TABLE FFMP, PRODUCT P2 WHERE P1.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P1.PROCESSSTEP_ID = FFMP.FGI_PROCESS_STEP_ID AND P2.PROCESSSTEP_ID = FFMP.FG_PROCESS_STEP_ID AND PS.PS_TYPE = 'Blank FGI' AND P1.PART_NUMBER = ? )" );
                pstmt = conn.prepareStatement( "SELECT ISNULL ( SUM( ISNULL( BACKLOG_QUANTITY, 0 ) ), 0 ) AS BACKLOG_QTY FROM FN_LOAD_WIP_FLUSH_BACKLOGS() WHERE BLANK_FGI_PART_NUMBER = ? " );
                pstmt.setString( 1, partNumber );
                rs = pstmt.executeQuery();

                if( rs.next() )
                {
                    backlogQuantity = rs.getInt("BACKLOG_QTY");
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not load Backlog Quantity for Product No = " + partNumber );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return backlogQuantity;
    }

    public static void calculateWipFlushQuantities( DBTransaction dbTransaction, int lotId ) throws ScalarException
    {
        Connection conn 		= null;
        CallableStatement cstmt	= null;
        ResultSet rs 			= null;
        boolean success         = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            //pstmt = conn.prepareStatement( "{ CALL sp_create_wip_flush_master( ? ) }" );
            cstmt = conn.prepareCall( "{ CALL SP_CREATE_NEW_WIP_FLUSH_MASTER( ? ) }" );
            cstmt.setInt( 1, lotId );
            cstmt.executeUpdate();
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not calculate Wip Flush Quantities");
        }
        finally
        {
            try
			{
				if( dbTransaction == null )
				{
					if ( conn != null )
                    {
						if ( success == true )
                        {
							conn.commit();
						}
						else
                        {
							conn.rollback();
						}
					}
				}
            }
            catch (SQLException ex)
			{
				ex.printStackTrace();
			}
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }





    public static void main( String args[] )
    {
		try
        {
			// getShippableUnitsByWeek( 0, 0, WIP_SHIPPABLE_UNITS );
			// getShippableUnitsByMonthByVendor( 0, 0, WIP_UNALLOCATED_UNITS );
			// getShippableUnitsByMonth( 0, 0, WIP_SHIPPABLE_UNITS );
			getWipInventoryByProduct(  null, 0, WIP_INVENTORY_UNITS, "true", null);
        }
        catch (ScalarException e)
        {
            e.printStackTrace();
        }
    }

    public static Workbook getWorkbookForWipFlushByProduct ( ArrayList baseProductList, String heading, String holdHeading, String vendorExpand, String lotExpand )
    {
        Workbook workbook   = null;
        Sheet   sheet       = null;
        int baseProductListSize = ( baseProductList == null ) ? 0 : baseProductList.size();
        if ( baseProductListSize > 0 )
        {
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                workbook    = new XSSFWorkbook();
            }
            else
            {
                workbook    = new HSSFWorkbook();
            }
            CreationHelper ch = workbook.getCreationHelper();
            DataFormat df = ch.createDataFormat();

            sheet       = workbook.createSheet( "Wip Flush By Product" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 10 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFontItalic = workbook.createFont();
            headerFontItalic.setFontHeightInPoints ( ( short ) 10 );
            headerFontItalic.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFontItalic.setFontName( "verdana" );
            headerFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            headerFontItalic.setItalic( true );

            Font textHeadFont = workbook.createFont();
            textHeadFont.setFontHeightInPoints ( ( short ) 10 );
            textHeadFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFont.setFontName( "verdana" );
            textHeadFont.setColor ( IndexedColors.BLUE.getIndex() );

            Font textHeadFontItalic = workbook.createFont();
            textHeadFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textHeadFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFontItalic.setFontName( "verdana" );
            textHeadFontItalic.setColor ( IndexedColors.BLUE.getIndex() );
            textHeadFontItalic.setItalic( true );

            Font textFont = workbook.createFont();
            textFont.setFontHeightInPoints ( ( short ) 10 );
            textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFont.setFontName( "verdana" );
            textFont.setColor ( IndexedColors.BLACK.getIndex());

            Font textFontItalic = workbook.createFont();
            textFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFontItalic.setFontName( "verdana" );
            textFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            textFontItalic.setItalic( Boolean.TRUE.booleanValue() );

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle headerValueStyle  = workbook.createCellStyle ( );
            headerValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueStyle.setWrapText ( true );
            headerValueStyle.setFont ( headerFont );
            headerValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle headerValueItalicStyle  = workbook.createCellStyle ( );
            headerValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueItalicStyle.setWrapText ( true );
            headerValueItalicStyle.setFont ( headerFontItalic );
            headerValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductStyle  = workbook.createCellStyle ( );
            baseProductStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            baseProductStyle.setWrapText ( true );
            baseProductStyle.setFont ( textHeadFont );

            CellStyle productStyle  = workbook.createCellStyle ( );
            productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            productStyle.setWrapText ( true );
            productStyle.setFont ( textFont );

            CellStyle baseProductValueStyle  = workbook.createCellStyle ( );
            baseProductValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueStyle.setWrapText ( true );
            baseProductValueStyle.setFont ( textHeadFont );
            baseProductValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductValueItalicStyle  = workbook.createCellStyle ( );
            baseProductValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueItalicStyle.setWrapText ( true );
            baseProductValueItalicStyle.setFont ( textHeadFontItalic );
            baseProductValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle lotNumberStyle  = workbook.createCellStyle ( );
            lotNumberStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderTop ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderRight ( CellStyle.BORDER_THIN );
            lotNumberStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            lotNumberStyle.setWrapText ( true );
            lotNumberStyle.setFont ( textFont );

            CellStyle productValueStyle  = workbook.createCellStyle ( );
            productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueStyle.setWrapText ( true );
            productValueStyle.setFont ( textFont );
            productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle productValueItalicStyle  = workbook.createCellStyle ( );
            productValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueItalicStyle.setWrapText ( true );
            productValueItalicStyle.setFont ( textFontItalic );
            productValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            //SETTING HEADINGS
            String headers[] = { "Part Number", "    Vendor Name     ", "Lot No#", "Fab", "Wafer Store", "Sort", "Die Bank", "Assembly", "     TEST     ",/* "     FPO     ", "Class Store",*/ "Finished Goods"};

            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)(headers.length-1), titleStyle );
            setCellValue ( cell, "Wip Flush - " + heading + " Report By Product " + holdHeading, titleStyle );

            row = sheet.createRow ( 2 );
            for ( int i = 0; i < headers.length; i++ )
            {
                cell    = row.createCell( (short)i );
                setCellValue ( cell, headers[i], headerStyle );
                int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
                sheet.setColumnWidth( (short)i, (short) ( ( ( width + 2 ) * 256 ) ) );
            }

            int fabTotalQty = 0;
            int waferStoreTotalQty = 0;
            int fabOptionQty = 0;
            int sortTotalQty = 0;
            int dieBankTotalQty = 0;
            int assemblyTotalQty = 0;
            int preTestTotalQty = 0;
            int testTotalQty = 0;
            int ftTotalQty = 0;
            int fpoTotalQty = 0;
            int classStoreTotalQty = 0;
            int fgTotalQty = 0;
            int nextRowNumber   = 3;
            int nextColumnIdx = 0;
            for ( int i = 0; i < baseProductListSize; i++ )
            {
                WipFlush baseProduct    =  ( WipFlush )baseProductList.get ( i );
                row = sheet.createRow( nextRowNumber++ );
                nextColumnIdx = 0;
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getPartNumber(), baseProductStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getFabQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getWaferStoreQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getSortQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getDieBankQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getAssemblyQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //setCellValue( row, (short)nextColumnIdx++, baseProduct.getPreTestQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getTestQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //setCellValue( row, (short)nextColumnIdx++, baseProduct.getFpoQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //setCellValue( row, (short)nextColumnIdx++, baseProduct.getClassStoreQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getFgQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );

                HashMap childMap    = baseProduct.getChildMap();
                Set keySet  = childMap.keySet();
                Iterator keyIterator = keySet.iterator();
                while ( keyIterator.hasNext() )
                {
                    WipFlush product    = ( WipFlush )childMap.get( keyIterator.next() );
                    row = sheet.createRow( nextRowNumber++ );
                    nextColumnIdx = 0;
                    setCellValue( row, (short)nextColumnIdx++, product.getPartNumber(), productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                    setCellValue( row, (short)nextColumnIdx++, product.getFabQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getWaferStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getSortQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC );
                    setCellValue( row, (short)nextColumnIdx++, product.getDieBankQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getAssemblyQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    //setCellValue( row, (short)nextColumnIdx++, baseProduct.getPreTestQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    //setCellValue( row, (short)nextColumnIdx++, product.getFpoQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    //setCellValue( row, (short)nextColumnIdx++, product.getClassStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getFgQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                    fabTotalQty += product.getFabQty() ;
                    waferStoreTotalQty += product.getWaferStoreQty();
                    /*fabOptionQty += product.getFabOptionQty();*/
                    sortTotalQty += product.getSortQty();
                    dieBankTotalQty += product.getDieBankQty();
                    assemblyTotalQty += product.getAssemblyQty();
                    //preTestTotalQty += product.getPreTestQty();
                    testTotalQty += product.getTestQty();
                    //fpoTotalQty += product.getFpoQty();
                    //classStoreTotalQty += product.getClassStoreQty();
                    fgTotalQty += product.getFgQty();

                    HashMap vendorMap = product.getChildMap();
                    if( vendorMap != null && "display=block".equalsIgnoreCase(vendorExpand))
                    {
                        Iterator vendorIt = vendorMap.keySet().iterator();
                        while( vendorIt.hasNext() )
                        {
                            WipFlush vendor = (WipFlush)vendorMap.get( vendorIt.next() );
                            row = sheet.createRow( nextRowNumber++ );
                            nextColumnIdx = 0;
                            setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            if( vendor.getVendor() != null ){
                                setCellValue( row, (short)nextColumnIdx++, vendor.getVendor(), productStyle  );
                            }
                            else{
                                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            }
                            setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getFabQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getWaferStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getSortQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getDieBankQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getAssemblyQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            //setCellValue( row, (short)nextColumnIdx++, vendor.getPreTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            //setCellValue( row, (short)nextColumnIdx++, vendor.getFpoQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            //setCellValue( row, (short)nextColumnIdx++, vendor.getClassStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getFgQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                            HashMap lotMap = vendor.getChildMap();
                            if( lotMap != null && "display=block".equalsIgnoreCase(lotExpand))
                            {
                                Iterator lotIt = lotMap.keySet().iterator();
                                while( lotIt.hasNext() )
                                {
                                    WipFlush lot = (WipFlush)lotMap.get( lotIt.next() );
                                    row = sheet.createRow( nextRowNumber++ );
                                    nextColumnIdx = 0;
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    if( lot.getLotNumber() != null)
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, lot.getLotNumber(), lotNumberStyle  );
                                    }
                                    else{
                                        setCellValue( row, (short)nextColumnIdx++, "", lotNumberStyle  );
                                    }
                                    setCellValue( row, (short)nextColumnIdx++, lot.getFabQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getWaferStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getSortQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getDieBankQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getAssemblyQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getPreTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    //setCellValue( row, (short)nextColumnIdx++, lot.getFpoQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    //setCellValue( row, (short)nextColumnIdx++, lot.getClassStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getFgQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                               }
                            }
                        }
                    }
                }
            }
            row = sheet.createRow( nextRowNumber++ );
            nextColumnIdx = 0;
            setCellValue( row, (short)nextColumnIdx++, "Grand Total", headerStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, fabTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, waferStoreTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, sortTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, dieBankTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, assemblyTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            //setCellValue( row, (short)nextColumnIdx++, preTestTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, testTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            //setCellValue( row, (short)nextColumnIdx++, fpoTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            //setCellValue( row, (short)nextColumnIdx++, classStoreTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, fgTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
        }
        return workbook;
    }
    /**
     * merges cells and sets each cell with the given cellStyle.
     * @param sheet
     * sheet object in which the cells has to be merged.
     * @param startRow
     * start row Number to merge from.<br>
     * starts from 0.
     * @param startCell
     * start cell number in the start row to merge from.<br>
     * starts from 0.
     * @param endRow
     * end row Number<br>
     * starts from 0.
     * @param endCell
     * end cell number<br>
     * starts from 0.
     * @param endCell
     * @return
     * returns cell object of the startRow, startCell.
     */
    private static Cell mergeCells ( Sheet sheet, int startRow, short startCell, int endRow, short endCell, CellStyle cellStyle )
    {
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell, endCell ) );
        Cell cell = null;
        for ( int rowIdx = startRow; rowIdx <= endRow; rowIdx++ )
        {
            for ( int cellIdx = startCell; cellIdx <= endCell; cellIdx++ )
            {
                cell = getCellAt ( sheet.getRow ( rowIdx ), (short)cellIdx );
                cell.setCellStyle ( cellStyle );
            }
        }
        return getCellAt ( sheet.getRow( startRow), startCell );
    }
    /**
     * Returns the cell object at the specified cell in the row.<br>
     * It creats cell if doesn't exists.
     * @param row
     * row object from which cell object has to be picked
     * @param cellNumber
     * starts from 0.
     * @return
     */
    private static Cell getCellAt ( Row row, short cellNumber )
    {
        Cell cell = null;
        cell = row.getCell( cellNumber );
        if ( cell == null )
        {
            cell = row.createCell ( cellNumber );
        }
        return cell;
    }
    private static void setCellValue ( Cell cell, String value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }

    private static void setCellValue ( Row row, short cellNo, String value, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }
    private static void setCellValue ( Row row, short cellNo, int value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }
    public static Workbook getWorkbookForWipFlushByVendor ( ArrayList vendorList, String heading, String holdHeading, String productExpand, String lotExpand )
    {
        Workbook workbook   = null;
        Sheet   sheet       = null;
        int vendorListSize = ( vendorList == null ) ? 0 : vendorList.size();
        if ( vendorListSize > 0 )
        {
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                workbook    = new XSSFWorkbook();
            }
            else
            {
                workbook    = new HSSFWorkbook();
            }
            CreationHelper ch = workbook.getCreationHelper();
            DataFormat df = ch.createDataFormat();

            sheet       = workbook.createSheet( "Wip Flush By Vendor" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 10 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFontItalic = workbook.createFont();
            headerFontItalic.setFontHeightInPoints ( ( short ) 10 );
            headerFontItalic.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFontItalic.setFontName( "verdana" );
            headerFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            headerFontItalic.setItalic( true );

            Font textHeadFont = workbook.createFont();
            textHeadFont.setFontHeightInPoints ( ( short ) 10 );
            textHeadFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFont.setFontName( "verdana" );
            textHeadFont.setColor ( IndexedColors.BLUE.getIndex() );

            Font textHeadFontItalic = workbook.createFont();
            textHeadFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textHeadFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFontItalic.setFontName( "verdana" );
            textHeadFontItalic.setColor ( IndexedColors.BLUE.getIndex() );
            textHeadFontItalic.setItalic( true );

            Font textFont = workbook.createFont();
            textFont.setFontHeightInPoints ( ( short ) 10 );
            textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFont.setFontName( "verdana" );
            textFont.setColor ( IndexedColors.BLACK.getIndex());

            Font textFontItalic = workbook.createFont();
            textFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFontItalic.setFontName( "verdana" );
            textFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            textFontItalic.setItalic( true );

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle headerValueStyle  = workbook.createCellStyle ( );
            headerValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueStyle.setWrapText ( true );
            headerValueStyle.setFont ( headerFont );
            headerValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle headerValueItalicStyle  = workbook.createCellStyle ( );
            headerValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueItalicStyle.setWrapText ( true );
            headerValueItalicStyle.setFont ( headerFontItalic );
            headerValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle vendorStyle  = workbook.createCellStyle ( );
            vendorStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            vendorStyle.setBorderTop ( CellStyle.BORDER_THIN );
            vendorStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            vendorStyle.setBorderRight ( CellStyle.BORDER_THIN );
            vendorStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            vendorStyle.setWrapText ( true );
            vendorStyle.setFont ( textHeadFont );

            CellStyle baseProductStyle  = workbook.createCellStyle ( );
            baseProductStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            baseProductStyle.setWrapText ( true );
            baseProductStyle.setFont ( headerFont );

            CellStyle baseProductValueStyle  = workbook.createCellStyle ( );
            baseProductValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueStyle.setWrapText ( true );
            baseProductValueStyle.setFont ( headerFont );
            baseProductValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductValueItalicStyle  = workbook.createCellStyle ( );
            baseProductValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueItalicStyle.setWrapText ( true );
            baseProductValueItalicStyle.setFont ( headerFontItalic );
            baseProductValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle productStyle  = workbook.createCellStyle ( );
            productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            productStyle.setWrapText ( true );
            productStyle.setFont ( textFont );

            CellStyle vendorValueStyle  = workbook.createCellStyle ( );
            vendorValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            vendorValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            vendorValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            vendorValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            vendorValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            vendorValueStyle.setWrapText ( true );
            vendorValueStyle.setFont ( textHeadFont );
            vendorValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle vendorValueItalicStyle  = workbook.createCellStyle ( );
            vendorValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            vendorValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            vendorValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            vendorValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            vendorValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            vendorValueItalicStyle.setWrapText ( true );
            vendorValueItalicStyle.setFont ( textHeadFontItalic );
            vendorValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle lotNumberStyle  = workbook.createCellStyle ( );
            lotNumberStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderTop ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderRight ( CellStyle.BORDER_THIN );
            lotNumberStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            lotNumberStyle.setWrapText ( true );
            lotNumberStyle.setFont ( textFont );

            CellStyle productValueStyle  = workbook.createCellStyle ( );
            productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueStyle.setWrapText ( true );
            productValueStyle.setFont ( textFont );
            productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle productValueItalicStyle  = workbook.createCellStyle ( );
            productValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueItalicStyle.setWrapText ( true );
            productValueItalicStyle.setFont ( textFontItalic );
            productValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            //SETTING HEADINGS
            String headers[] = { "    Vendor Name     ", "Part Number", "Lot No#", "Fab", "Wafer Store", "Sort", "Die Bank", "Assembly", "     TEST     ",/* "     FPO     ", "Class Store",*/ "Finished Goods"};

            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)(headers.length-1), titleStyle );
            setCellValue ( cell, "Wip Flush - " + heading + " Report By Vendor " + holdHeading, titleStyle );

            row = sheet.createRow ( 2 );
            for ( int i = 0; i < headers.length; i++ )
            {
                cell    = row.createCell( (short)i );
                setCellValue ( cell, headers[i], headerStyle );
                int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
                sheet.setColumnWidth( (short)i, (short) ( ( ( width + 2 ) * 256 ) ) );
            }

            int fabTotalQty = 0;
            int waferStoreTotalQty = 0;
            int fabOptionQty = 0;
            int sortTotalQty = 0;
            int dieBankTotalQty = 0;
            int assemblyTotalQty = 0;
            int preTestTotalQty = 0;
            int testTotalQty = 0;
            int ftTotalQty = 0;
            int fpoTotalQty = 0;
            int classStoreTotalQty = 0;
            int fgTotalQty = 0;
            int nextRowNumber   = 3;
            int nextColumnIdx   = 0;
            for ( int i = 0; i < vendorListSize; i++ )
            {
                WipFlush vendor    =  ( WipFlush )vendorList.get ( i );
                row = sheet.createRow( nextRowNumber++ );
                nextColumnIdx = 0;
                setCellValue( row, (short)nextColumnIdx++, vendor.getVendor(), vendorStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", vendorValueStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", vendorValueStyle  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getFabQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getWaferStoreQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getSortQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getDieBankQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getAssemblyQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //setCellValue( row, (short)nextColumnIdx++, vendor.getPreTestQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getTestQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //setCellValue( row, (short)nextColumnIdx++, vendor.getFpoQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //setCellValue( row, (short)nextColumnIdx++, vendor.getClassStoreQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getFgQty(), vendorValueStyle, Cell.CELL_TYPE_NUMERIC  );

                HashMap baseProductMap    = vendor.getChildMap();
                Set keySet  = baseProductMap.keySet();
                Iterator keyIterator = keySet.iterator();
                while ( keyIterator.hasNext() )
                {
                    WipFlush baseProduct    = ( WipFlush )baseProductMap.get( keyIterator.next() );
                    row = sheet.createRow( nextRowNumber++ );
                    nextColumnIdx = 0;
                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getPartNumber(), baseProductStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getFabQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getWaferStoreQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getSortQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getDieBankQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getAssemblyQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    //setCellValue( row, (short)nextColumnIdx++, baseProduct.getPreTestQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getTestQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    //setCellValue( row, (short)nextColumnIdx++, baseProduct.getFpoQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    //setCellValue( row, (short)nextColumnIdx++, baseProduct.getClassStoreQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getFgQty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );

                    HashMap productMap = baseProduct.getChildMap();
                    Set productkeySet  = productMap.keySet();
                    if (productMap != null &&  "display=block".equalsIgnoreCase(productExpand))
                    {
                        Iterator productkeyIterator = productkeySet.iterator();
                        while ( productkeyIterator.hasNext() )
                        {
                            WipFlush product    = ( WipFlush )productMap.get( productkeyIterator.next() );
                            row = sheet.createRow( nextRowNumber++ );
                            nextColumnIdx = 0;
                            setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            setCellValue( row, (short)nextColumnIdx++, product.getPartNumber(), productStyle  );
                            setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                            setCellValue( row, (short)nextColumnIdx++, product.getFabQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getWaferStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getSortQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getDieBankQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getAssemblyQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            //setCellValue( row, (short)nextColumnIdx++, product.getPreTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            //setCellValue( row, (short)nextColumnIdx++, product.getFpoQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            //setCellValue( row, (short)nextColumnIdx++, product.getClassStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getFgQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                            fabTotalQty += product.getFabQty() ;
                            waferStoreTotalQty += product.getWaferStoreQty();
                            /*fabOptionQty += product.getFabOptionQty();*/
                            sortTotalQty += product.getSortQty();
                            dieBankTotalQty += product.getDieBankQty();
                            assemblyTotalQty += product.getAssemblyQty();
                            testTotalQty += product.getTestQty();
                            //preTestTotalQty += product.getPreTestQty();
                            //fpoTotalQty += product.getFpoQty();
                            //classStoreTotalQty += product.getClassStoreQty();
                            fgTotalQty += product.getFgQty();

                            HashMap lotMap = product.getChildMap();
                            if( lotMap != null && "display=block".equalsIgnoreCase(lotExpand))
                            {
                                Iterator lotIt = lotMap.keySet().iterator();
                                while( lotIt.hasNext() )
                                {
                                    WipFlush lot = (WipFlush)lotMap.get( lotIt.next() );
                                    row = sheet.createRow( nextRowNumber++ );
                                    nextColumnIdx = 0;
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    if( lot.getLotNumber() != null)
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, lot.getLotNumber(), lotNumberStyle  );
                                    }
                                    else
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, "", lotNumberStyle  );
                                    }
                                    setCellValue( row, (short)nextColumnIdx++, lot.getFabQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getWaferStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getSortQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getDieBankQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getAssemblyQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    //setCellValue( row, (short)nextColumnIdx++, lot.getPreTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getTestQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    //setCellValue( row, (short)nextColumnIdx++, lot.getFpoQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    //setCellValue( row, (short)nextColumnIdx++, lot.getClassStoreQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getFgQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                               }
                            }
                        }
                    }
                }
            }
            row = sheet.createRow( nextRowNumber++ );
            nextColumnIdx = 0;
            setCellValue( row, (short)nextColumnIdx++, "Grand Total", headerStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, fabTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, (short)nextColumnIdx++, waferStoreTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, (short)nextColumnIdx++, sortTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, dieBankTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, assemblyTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, preTestTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, testTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            //setCellValue( row, (short)nextColumnIdx++, fpoTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            //setCellValue( row, (short)nextColumnIdx++, classStoreTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, fgTotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
        }
        return workbook;
    }

    /**
     *
     * @param dbTransaction
     * @return
     * returns the backlog map which contains the blank FGI part number as key and total backlog qty(String) as value
     * @throws ScalarException
     */
    public static HashMap loadBacklogQuantityMapByBFGI( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        ResultSet rset      = null;
        boolean success     = false;

        HashMap backlogMap  = new HashMap();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT BLANK_FGI_PART_NUMBER, ISNULL ( SUM( ISNULL( BACKLOG_QUANTITY, 0 ) ), 0 ) AS BACKLOG_QTY FROM FN_LOAD_WIP_FLUSH_BACKLOGS() GROUP BY BLANK_FGI_PART_NUMBER" );
            rset = pstmt.executeQuery();
            String bfgi = null;
            String backlogQty = null;
            while ( rset.next() )
            {
                bfgi        = rset.getString ( "BLANK_FGI_PART_NUMBER" );
                backlogQty  = rset.getString ( "BACKLOG_QTY" );
                backlogMap.put ( bfgi, backlogQty );
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load backlog qty map", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch( SQLException ignore ) {}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) {}
            }
        }
        return backlogMap;
    }

    /**
     * To sort the map in the key order.<br>
     * The passed map should be LinkedHashMap to preserve the correct order other wise no guarantee.
     * @param map
     * The map to be sorted
     */
    private static void sortMapByKeys( LinkedHashMap map )
    {
        if ( map == null )
        {
            return;
        }
        List mapKeys    = new ArrayList ( map.keySet() );
        List mapValues  = new ArrayList ( map.values() );
        map.clear();
        TreeSet sortedKeysSet = new TreeSet( mapKeys );
        Object[] sortedKeysArray = sortedKeysSet.toArray();
        int sortedKeysArraySize = sortedKeysArray.length;
        for ( int i=0; i < sortedKeysArraySize; i++ )
        {
            map.put ( sortedKeysArray[i], mapValues.get ( mapKeys.indexOf( sortedKeysArray[i] ) ) );
        }
    }

    public static HashMap getProductVendorWipMap(DBTransaction dbTransaction, int holdType, int[] baseProductIds, int[] fgProcessStepIds) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        HashMap productVendorWipMap = new HashMap();
        LinkedHashMap vendorMap = null;
        LinkedHashMap lotMap    = null;
        WipFlush vendorWip      = null;
        WipFlush lotWip         = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            StringBuffer baseProducts = new StringBuffer();
            if ( baseProductIds != null )
            {
                for ( int i=0; i < baseProductIds.length; i++ )
                {
                    if ( baseProductIds[i] == 0 )
                    {
                        baseProducts = null;
                        break;
                    }
                    else
                    {
                        baseProducts.append( baseProductIds[i] ).append( "," );
                    }
                }
                if ( baseProducts != null )
                {
                    baseProducts.deleteCharAt( baseProducts.lastIndexOf(",") );
                }
            }
            else
            {
                baseProducts = null;
            }
            StringBuffer fgProcessSteps = new StringBuffer();
            if ( fgProcessStepIds != null )
            {
                for ( int i=0; i < fgProcessStepIds.length; i++ )
                {
                    if ( fgProcessStepIds[i] == 0 )
                    {
                        fgProcessSteps = null;
                        break;
                    }
                    else
                    {
                        fgProcessSteps.append( fgProcessStepIds[i] ).append( "," );
                    }
                }
                if ( fgProcessSteps != null )
                {
                    fgProcessSteps.deleteCharAt( fgProcessSteps.lastIndexOf(",") );
                }
            }
            else
            {
                fgProcessSteps = null;
            }
            pstmt = conn.prepareStatement ( "SELECT PART_NUMBER, VENDOR_NAME, LOT_NUMBER, QTY_IN, DATE_IN, IS_REGULAR_INTRANSIT, PS_TYPE_NAME, PS_TYPE  FROM FN_LOAD_ALL_WIP(?,?, ?)" );
            pstmt.setInt ( 1, holdType );
            pstmt.setString ( 2, baseProducts == null ? null : baseProducts.toString() );
            pstmt.setString ( 3, fgProcessSteps == null ? null : fgProcessSteps.toString() );

            rset = pstmt.executeQuery();
            ArrayList lotList = new ArrayList();
            LotView lotInfo = null;
            while ( rset.next() )
            {
                lotInfo = new LotView();
                lotInfo.setPartNumber( rset.getString ( "PART_NUMBER" ) );
                lotInfo.setVendorLocationName( rset.getString ( "VENDOR_NAME" ) );
                lotInfo.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                lotInfo.setQuantityIn( rset.getInt ( "QTY_IN" ) );
                lotInfo.setDateIn( rset.getString ( "DATE_IN" ) );
                lotInfo.setIsRegularIntransitProgrelease( rset.getInt ( "IS_REGULAR_INTRANSIT" ) );
                lotInfo.setProcessTypeName( rset.getString ( "PS_TYPE_NAME" ) );
                lotInfo.setProcessType( rset.getString ( "PS_TYPE" ) );

                if ( lotInfo.getQuantityIn() > 0 )
                {
                    lotList.add ( lotInfo );
                }
            }
            int lotListSize = lotList.size();
            for ( int i=0; i < lotListSize; i++ )
            {
                lotInfo = (LotView)lotList.get ( i );
                vendorMap = (LinkedHashMap)productVendorWipMap.get ( lotInfo.getPartNumber() );
                if ( vendorMap == null )
                {
                    vendorMap = new LinkedHashMap();
                    productVendorWipMap.put ( lotInfo.getPartNumber(), vendorMap );
                }

                vendorWip = (WipFlush)vendorMap.get( lotInfo.getVendorLocationName() );
                if ( vendorWip == null )
                {
                    vendorWip = new WipFlush();
                    vendorWip.setVendor ( lotInfo.getVendorLocationName() );
                    vendorWip.setChildMap( new LinkedHashMap() );

                    vendorMap.put ( lotInfo.getVendorLocationName(), vendorWip );
                }

                lotMap = vendorWip.getChildMap();
                lotWip = (WipFlush)lotMap.get( lotInfo.getLotNumber() );
                if ( lotWip == null )
                {
                    lotWip = new WipFlush();
                    lotWip.setLotNumber ( lotInfo.getLotNumber() );
                    lotMap.put ( lotInfo.getLotNumber(), lotWip );
                }
                if ( lotInfo.getIsRegularIntransitProgrelease() == 0 )
                {
                    setWipQty( lotWip, lotInfo.getProcessType(), lotInfo.getQuantityIn() );
                }
                else //INTRANSIT QTY
                {
                    //todo need to set the respective process intransit qty..
                }
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load all processes wip", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return productVendorWipMap;
    }

    public static boolean createWipFlushSetup( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn 		= null;
        CallableStatement cstmt	= null;
        boolean success         = false;

        try
        {
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "{ CALL SP_CREATE_WIP_FLUSH_SETUP(0) }" );
            cstmt.executeUpdate();
            if( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not calculate Wip Flush shippables");
        }
        finally
        {
            if( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( !success  )
                    {
                        try { conn.rollback(); } catch ( SQLException ignore ){}
                    }
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return success;
    }

    public static HashMap getShippableVendorWipMapByWeek(DBTransaction dbTransaction, int holdType, Date wwStartDate, int[] fgProcessStepIds) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        String vendorName		= null;
		String lotNumber 		= null;
        String fgPartNumber     = null;
        Date fg_date			= null;
		int fgQty 				= 0;
		int lotId				= 0;
        WipFlushByDate vendorWip = null;
        WipFlushByDate lotWip   = null;
        LinkedHashMap vendorMap       = null;
        LinkedHashMap lotMap          = null;
        HashMap shippableMapByWeek    = new HashMap();
        StringBuffer fgProcessSteps = new StringBuffer();
        if ( fgProcessStepIds != null )
        {
            for ( int i=0; i < fgProcessStepIds.length; i++ )
            {
                if ( fgProcessStepIds[i] == 0 )
                {
                    fgProcessSteps = null;
                    break;
                }
                else
                {
                    fgProcessSteps.append( fgProcessStepIds[i] ).append( "," );
                }
            }
            if ( fgProcessSteps != null )
            {
                fgProcessSteps.deleteCharAt( fgProcessSteps.lastIndexOf(",") );
            }
        }
        else
        {
            fgProcessSteps = null;
        }
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if ( holdType == 1 )
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT WM.LOT_ID, LTRIM( RTRIM( WM.LOT_NUMBER ) ) AS LOT_NUMBER, L.L_VENDOR_NAME, WM.FG_QTY, WM.DATE_IN AS DATE_IN, WM.FG_DATE AS EXPECTED_DATE_OUT, P.PART_NUMBER AS FG_PART_NUMBER FROM WIP_MASTER WM, LOCATION L, PRODUCT P (NOLOCK) WHERE WM.LOCATION_ID = L.LOCATION_ID AND P.PROCESSSTEP_ID = WM.FG_PROCESSSTEP_ID AND WM.IS_CONSIDER_FOR_REPORT = 0" + (fgProcessSteps==null ? "" : " AND WM.FG_PROCESSSTEP_ID IN (" + fgProcessSteps + ")") );
            }
            else if ( holdType == 2 )
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT WM.LOT_ID, LTRIM( RTRIM( WM.LOT_NUMBER ) ) AS LOT_NUMBER, L.L_VENDOR_NAME, WM.FG_QTY, WM.DATE_IN AS DATE_IN, WM.FG_DATE AS EXPECTED_DATE_OUT, P.PART_NUMBER AS FG_PART_NUMBER FROM WIP_MASTER WM, LOT_TABLE LT, LOCATION L, PRODUCT P (NOLOCK) WHERE WM.LOT_ID = LT.LOT_ID AND WM.LOCATION_ID = L.LOCATION_ID AND LT.LOT_CURRENT_STATUS <> 'HOLD' AND P.PROCESSSTEP_ID = WM.FG_PROCESSSTEP_ID AND WM.IS_CONSIDER_FOR_REPORT = 0" + (fgProcessSteps==null ? "" : " AND WM.FG_PROCESSSTEP_ID IN (" + fgProcessSteps + ")") );
            }
            else
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT WM.LOT_ID, LTRIM( RTRIM( WM.LOT_NUMBER ) ) AS LOT_NUMBER, L.L_VENDOR_NAME, WM.FG_QTY, WM.DATE_IN AS DATE_IN, WM.FG_DATE AS EXPECTED_DATE_OUT, P.PART_NUMBER AS FG_PART_NUMBER FROM WIP_MASTER WM, LOT_TABLE LT, LOCATION L, PRODUCT P (NOLOCK) WHERE WM.LOT_ID = LT.LOT_ID AND WM.LOCATION_ID = L.LOCATION_ID AND LT.LOT_CURRENT_STATUS = 'HOLD' AND P.PROCESSSTEP_ID = WM.FG_PROCESSSTEP_ID AND WM.IS_CONSIDER_FOR_REPORT = 0" + (fgProcessSteps==null ? "" : " AND WM.FG_PROCESSSTEP_ID IN (" + fgProcessSteps + ")") );
            }

            rset 	  = pstmt.executeQuery();
            while( rset.next() )
            {
                lotId 		= rset.getInt( "LOT_ID" );
                lotNumber 	= rset.getString( "LOT_NUMBER" );
                vendorName 	= rset.getString( "L_VENDOR_NAME" );
                fgQty 		= rset.getInt( "FG_QTY" );
                fg_date 	= rset.getDate( "EXPECTED_DATE_OUT" );
                fgPartNumber = rset.getString ( "FG_PART_NUMBER" );

                if( fg_date == null )
                {
                    fg_date = new Date();
                }

                if( fgQty > 0 )
                {
                    vendorMap = (LinkedHashMap)shippableMapByWeek.get ( fgPartNumber );
                    if ( vendorMap == null )
                    {
                        vendorMap = new LinkedHashMap();
                        shippableMapByWeek.put ( fgPartNumber, vendorMap );
                    }

                    vendorWip = (WipFlushByDate) vendorMap.get ( vendorName );
                    if ( vendorWip == null )
                    {
                        vendorWip = new WipFlushByDate();
                        vendorWip.setVendor( vendorName );
                        vendorMap.put( vendorName, vendorWip );

                        vendorWip.setChildMap( new LinkedHashMap() );
                    }
                    lotMap = vendorWip.getChildMap();
                    lotWip = (WipFlushByDate)lotMap.get( lotNumber );
                    if ( lotWip == null )
                    {
                        lotWip = new WipFlushByDate();
                        lotWip.setLotNumber( lotNumber );
                        lotMap.put( lotNumber, lotWip );
                    }
                    setWipQtyByDate( lotWip, fg_date, fgQty, wwStartDate);
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Shippable By Week Lot Information could not be fetched", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return shippableMapByWeek;
    }

    public static HashMap getShippableVendorWipMapByMonth(DBTransaction dbTransaction, int holdType, int[] fgProcessStepIds) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        String vendorName		= null;
		String lotNumber 		= null;
        String fgPartNumber     = null;
        Date fg_date			= null;
		int fgQty 				= 0;
		int lotId				= 0;
        WipFlushByMonth vendorWip = null;
        WipFlushByMonth lotWip   = null;
        LinkedHashMap vendorMap       = null;
        LinkedHashMap lotMap          = null;
        HashMap shippableMapByMonth    = new HashMap();
        StringBuffer fgProcessSteps = new StringBuffer();
        if ( fgProcessStepIds != null )
        {
            for ( int i=0; i < fgProcessStepIds.length; i++ )
            {
                if ( fgProcessStepIds[i] == 0 )
                {
                    fgProcessSteps = null;
                    break;
                }
                else
                {
                    fgProcessSteps.append( fgProcessStepIds[i] ).append( "," );
                }
            }
            if ( fgProcessSteps != null )
            {
                fgProcessSteps.deleteCharAt( fgProcessSteps.lastIndexOf(",") );
            }
        }
        else
        {
            fgProcessSteps = null;
        }
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if ( holdType == 1 )
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT WM.LOT_ID, LTRIM( RTRIM( WM.LOT_NUMBER ) ) AS LOT_NUMBER, L.L_VENDOR_NAME, WM.FG_QTY, WM.DATE_IN AS DATE_IN, WM.FG_DATE AS EXPECTED_DATE_OUT, P.PART_NUMBER AS FG_PART_NUMBER FROM WIP_MASTER WM, LOCATION L, PRODUCT P (NOLOCK) WHERE WM.LOCATION_ID = L.LOCATION_ID AND P.PROCESSSTEP_ID = WM.FG_PROCESSSTEP_ID AND WM.IS_CONSIDER_FOR_REPORT = 0" + (fgProcessSteps==null ? "" : " AND WM.FG_PROCESSSTEP_ID IN (" + fgProcessSteps + ")") );
            }
            else if ( holdType == 2 )
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT WM.LOT_ID, LTRIM( RTRIM( WM.LOT_NUMBER ) ) AS LOT_NUMBER, L.L_VENDOR_NAME, WM.FG_QTY, WM.DATE_IN AS DATE_IN, WM.FG_DATE AS EXPECTED_DATE_OUT, P.PART_NUMBER AS FG_PART_NUMBER FROM WIP_MASTER WM, LOT_TABLE LT, LOCATION L, PRODUCT P (NOLOCK) WHERE WM.LOT_ID = LT.LOT_ID AND WM.LOCATION_ID = L.LOCATION_ID AND LT.LOT_CURRENT_STATUS <> 'HOLD' AND P.PROCESSSTEP_ID = WM.FG_PROCESSSTEP_ID AND WM.IS_CONSIDER_FOR_REPORT = 0" + (fgProcessSteps==null ? "" : " AND WM.FG_PROCESSSTEP_ID IN (" + fgProcessSteps + ")") );
            }
            else
            {
                pstmt = conn.prepareStatement( "SELECT DISTINCT WM.LOT_ID, LTRIM( RTRIM( WM.LOT_NUMBER ) ) AS LOT_NUMBER, L.L_VENDOR_NAME, WM.FG_QTY, WM.DATE_IN AS DATE_IN, WM.FG_DATE AS EXPECTED_DATE_OUT, P.PART_NUMBER AS FG_PART_NUMBER FROM WIP_MASTER WM, LOT_TABLE LT, LOCATION L, PRODUCT P (NOLOCK) WHERE WM.LOT_ID = LT.LOT_ID AND WM.LOCATION_ID = L.LOCATION_ID AND LT.LOT_CURRENT_STATUS = 'HOLD' AND P.PROCESSSTEP_ID = WM.FG_PROCESSSTEP_ID AND WM.IS_CONSIDER_FOR_REPORT = 0" + (fgProcessSteps==null ? "" : " AND WM.FG_PROCESSSTEP_ID IN (" + fgProcessSteps + ")") );
            }

            rset 	  = pstmt.executeQuery();
            while( rset.next() )
            {
                lotId 		= rset.getInt( "LOT_ID" );
                lotNumber 	= rset.getString( "LOT_NUMBER" );
                vendorName 	= rset.getString( "L_VENDOR_NAME" );
                fgQty 		= rset.getInt( "FG_QTY" );
                fg_date 	= rset.getDate( "EXPECTED_DATE_OUT" );
                fgPartNumber = rset.getString ( "FG_PART_NUMBER" );

                if( fg_date == null )
                {
                    fg_date = new Date();
                }

                if( fgQty > 0 )
                {
                    vendorMap = (LinkedHashMap)shippableMapByMonth.get ( fgPartNumber );
                    if ( vendorMap == null )
                    {
                        vendorMap = new LinkedHashMap();
                        shippableMapByMonth.put ( fgPartNumber, vendorMap );
                    }

                    vendorWip = (WipFlushByMonth) vendorMap.get ( vendorName );
                    if ( vendorWip == null )
                    {
                        vendorWip = new WipFlushByMonth();
                        vendorWip.setVendor( vendorName );
                        vendorMap.put( vendorName, vendorWip );

                        vendorWip.setChildMap( new LinkedHashMap() );
                    }
                    lotMap = vendorWip.getChildMap();
                    lotWip = (WipFlushByMonth)lotMap.get( lotNumber );
                    if ( lotWip == null )
                    {
                        lotWip = new WipFlushByMonth();
                        lotWip.setLotNumber( lotNumber );
                        lotMap.put( lotNumber, lotWip );
                    }
                    setWipQtyByMonth( lotWip, fg_date, fgQty);
                }
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Shippable By Month Lot Information could not be fetched", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return shippableMapByMonth;
    }

    public static Workbook getWorkbookForWipFlushShippableByProductWeek ( ArrayList baseProductList, String heading, String holdHeading, int startingWWNumber, int wwYear, int noOfWeeks, String vendorExpand, String lotExpand )
    {
        noOfWeeks = (noOfWeeks == 0) ? 13 : noOfWeeks;
        Workbook workbook   = null;
        Sheet   sheet       = null;
        int baseProductListSize = ( baseProductList == null ) ? 0 : baseProductList.size();
        if ( baseProductListSize > 0 )
        {
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                workbook    = new XSSFWorkbook();
            }
            else
            {
                workbook    = new HSSFWorkbook();
            }
            CreationHelper ch = workbook.getCreationHelper();
            DataFormat df = ch.createDataFormat();

            sheet       = workbook.createSheet( "Wip Flush Shippable By Product" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 10 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFontItalic = workbook.createFont();
            headerFontItalic.setFontHeightInPoints ( ( short ) 10 );
            headerFontItalic.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFontItalic.setFontName( "verdana" );
            headerFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            headerFontItalic.setItalic( true );

            Font textHeadFont = workbook.createFont();
            textHeadFont.setFontHeightInPoints ( ( short ) 10 );
            textHeadFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFont.setFontName( "verdana" );
            textHeadFont.setColor ( IndexedColors.BLUE.getIndex() );

            Font textHeadFontItalic = workbook.createFont();
            textHeadFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textHeadFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFontItalic.setFontName( "verdana" );
            textHeadFontItalic.setColor ( IndexedColors.BLUE.getIndex() );
            textHeadFontItalic.setItalic( true );

            Font textFont = workbook.createFont();
            textFont.setFontHeightInPoints ( ( short ) 10 );
            textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFont.setFontName( "verdana" );
            textFont.setColor ( IndexedColors.BLACK.getIndex());

            Font textFontItalic = workbook.createFont();
            textFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFontItalic.setFontName( "verdana" );
            textFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            textFontItalic.setItalic( Boolean.TRUE.booleanValue() );

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle headerValueStyle  = workbook.createCellStyle ( );
            headerValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueStyle.setWrapText ( true );
            headerValueStyle.setFont ( headerFont );
            headerValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle headerValueItalicStyle  = workbook.createCellStyle ( );
            headerValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueItalicStyle.setWrapText ( true );
            headerValueItalicStyle.setFont ( headerFontItalic );
            headerValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductStyle  = workbook.createCellStyle ( );
            baseProductStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            baseProductStyle.setWrapText ( true );
            baseProductStyle.setFont ( textHeadFont );

            CellStyle productStyle  = workbook.createCellStyle ( );
            productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            productStyle.setWrapText ( true );
            productStyle.setFont ( textFont );

            CellStyle baseProductValueStyle  = workbook.createCellStyle ( );
            baseProductValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueStyle.setWrapText ( true );
            baseProductValueStyle.setFont ( textHeadFont );
            baseProductValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductValueItalicStyle  = workbook.createCellStyle ( );
            baseProductValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueItalicStyle.setWrapText ( true );
            baseProductValueItalicStyle.setFont ( textHeadFontItalic );
            baseProductValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle lotNumberStyle  = workbook.createCellStyle ( );
            lotNumberStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderTop ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderRight ( CellStyle.BORDER_THIN );
            lotNumberStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            lotNumberStyle.setWrapText ( true );
            lotNumberStyle.setFont ( textFont );

            CellStyle productValueStyle  = workbook.createCellStyle ( );
            productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueStyle.setWrapText ( true );
            productValueStyle.setFont ( textFont );
            productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle productValueItalicStyle  = workbook.createCellStyle ( );
            productValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueItalicStyle.setWrapText ( true );
            productValueItalicStyle.setFont ( textFontItalic );
            productValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            //SETTING HEADINGS
            String headers[] = new String[ noOfWeeks+3 ];
            headers[0] = "Part Number";
            headers[1] = "    Vendor Name     ";
            headers[2] = "Lot No#";
            int wwNumber = startingWWNumber;
            for (int k = 0; k < noOfWeeks; k++)
            {
                if (wwNumber == 53 && k > 0)
                {
                    Calendar cal = Calendar.getInstance();
                    cal.set(wwYear, Calendar.DECEMBER, 31);
                    WorkWeek workWeek = WorkWeek.getWorkWeekInstance(cal.getTime());
                    if (workWeek.getCurrentWWNumber() != wwNumber) //Work week 53 not present
                    {
                        wwNumber = 1;
                        wwYear = wwYear + 1;
                    }
                } else if (wwNumber > 53) {
                    wwNumber = 1;
                    wwYear = wwYear + 1;
                }
                if (k != 25)
                {
                    headers[k+3] = "WW-" + wwNumber;
                }
                else
                {
                    headers[k+3] = "WW-" + wwNumber + " to ~";
                }
                wwNumber++;
            }
            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)(headers.length-1), titleStyle );
            setCellValue ( cell, "Wip Flush - " + heading + " Report By Product " + holdHeading, titleStyle );

            row = sheet.createRow ( 2 );
            for ( int i = 0; i < headers.length; i++ )
            {
                cell    = row.createCell( (short)i );
                setCellValue ( cell, headers[i], headerStyle );
                int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
                sheet.setColumnWidth( (short)i, (short) ( ( ( width + 2 ) * 256 ) ) );
            }

            int nextRowNumber   = 3;
            int nextColumnIdx = 0;
            int[] allWeekTotalQty = new int[26];
            for ( int i = 0; i < baseProductListSize; i++ )
            {
                WipFlushByDate baseProduct    =  ( WipFlushByDate )baseProductList.get ( i );
                row = sheet.createRow( nextRowNumber++ );
                nextColumnIdx = 0;
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getPartNumber(), baseProductStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                for ( int k = 0; k < noOfWeeks; k++ )
                {
                    allWeekTotalQty[k] = allWeekTotalQty[k] + baseProduct.getAllWeekQty()[k];
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getAllWeekQty()[ k ], baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                }

                HashMap childMap    = baseProduct.getChildMap();
                Set keySet  = childMap.keySet();
                Iterator keyIterator = keySet.iterator();
                while ( keyIterator.hasNext() )
                {
                    WipFlushByDate product    = ( WipFlushByDate )childMap.get( keyIterator.next() );
                    row = sheet.createRow( nextRowNumber++ );
                    nextColumnIdx = 0;
                    setCellValue( row, (short)nextColumnIdx++, product.getPartNumber(), productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                    for ( int k = 0; k < noOfWeeks; k++ )
                    {
                        setCellValue( row, (short)nextColumnIdx++, product.getAllWeekQty()[ k ], productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    }

                    HashMap vendorMap = product.getChildMap();
                    if( vendorMap != null && "display=block".equalsIgnoreCase(vendorExpand) )
                    {
                        Iterator vendorIt = vendorMap.keySet().iterator();
                        while( vendorIt.hasNext() )
                        {
                            WipFlushByDate vendor = (WipFlushByDate)vendorMap.get( vendorIt.next() );
                            row = sheet.createRow( nextRowNumber++ );
                            nextColumnIdx = 0;
                            setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            if( vendor.getVendor() != null ){
                                setCellValue( row, (short)nextColumnIdx++, vendor.getVendor(), productStyle  );
                            }
                            else{
                                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            }
                            setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                            for ( int k = 0; k < noOfWeeks; k++ )
                            {
                                setCellValue( row, (short)nextColumnIdx++, vendor.getAllWeekQty()[k], productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            }
                            HashMap lotMap = vendor.getChildMap();
                            if( lotMap != null && "display=block".equalsIgnoreCase(lotExpand))
                            {
                                Iterator lotIt = lotMap.keySet().iterator();
                                while( lotIt.hasNext() )
                                {
                                    WipFlushByDate lot = (WipFlushByDate)lotMap.get( lotIt.next() );
                                    row = sheet.createRow( nextRowNumber++ );
                                    nextColumnIdx = 0;
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    if( lot.getLotNumber() != null)
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, lot.getLotNumber(), lotNumberStyle  );
                                    }
                                    else{
                                        setCellValue( row, (short)nextColumnIdx++, "", lotNumberStyle  );
                                    }
                                    for ( int k = 0; k < noOfWeeks; k++ )
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, lot.getAllWeekQty()[k], productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    }
                               }
                            }
                        }
                    }
                }
            }
            row = sheet.createRow( nextRowNumber++ );
            nextColumnIdx = 0;
            setCellValue( row, (short)nextColumnIdx++, "Grand Total", headerStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            for ( int k = 0; k < noOfWeeks; k++ )
            {
                setCellValue( row, (short)nextColumnIdx++, allWeekTotalQty[k], headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            }
        }
        return workbook;
    }
    public static Workbook getWorkbookForWipFlushShippableByProductMonth ( ArrayList baseProductList, String heading, String holdHeading, String vendorExpand, String lotExpand )
    {
        Workbook workbook   = null;
        Sheet   sheet       = null;
        int baseProductListSize = ( baseProductList == null ) ? 0 : baseProductList.size();
        if ( baseProductListSize > 0 )
        {
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                workbook    = new XSSFWorkbook();
            }
            else
            {
                workbook    = new HSSFWorkbook();
            }
            CreationHelper ch = workbook.getCreationHelper();
            DataFormat df = ch.createDataFormat();

            sheet       = workbook.createSheet( "Wip Flush Shippable By Product" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 10 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFontItalic = workbook.createFont();
            headerFontItalic.setFontHeightInPoints ( ( short ) 10 );
            headerFontItalic.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFontItalic.setFontName( "verdana" );
            headerFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            headerFontItalic.setItalic( true );

            Font textHeadFont = workbook.createFont();
            textHeadFont.setFontHeightInPoints ( ( short ) 10 );
            textHeadFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFont.setFontName( "verdana" );
            textHeadFont.setColor ( IndexedColors.BLUE.getIndex() );

            Font textHeadFontItalic = workbook.createFont();
            textHeadFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textHeadFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFontItalic.setFontName( "verdana" );
            textHeadFontItalic.setColor ( IndexedColors.BLUE.getIndex() );
            textHeadFontItalic.setItalic( true );

            Font textFont = workbook.createFont();
            textFont.setFontHeightInPoints ( ( short ) 10 );
            textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFont.setFontName( "verdana" );
            textFont.setColor ( IndexedColors.BLACK.getIndex());

            Font textFontItalic = workbook.createFont();
            textFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFontItalic.setFontName( "verdana" );
            textFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            textFontItalic.setItalic( Boolean.TRUE.booleanValue() );

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle headerValueStyle  = workbook.createCellStyle ( );
            headerValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueStyle.setWrapText ( true );
            headerValueStyle.setFont ( headerFont );
            headerValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle headerValueItalicStyle  = workbook.createCellStyle ( );
            headerValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueItalicStyle.setWrapText ( true );
            headerValueItalicStyle.setFont ( headerFontItalic );
            headerValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductStyle  = workbook.createCellStyle ( );
            baseProductStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            baseProductStyle.setWrapText ( true );
            baseProductStyle.setFont ( textHeadFont );

            CellStyle productStyle  = workbook.createCellStyle ( );
            productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            productStyle.setWrapText ( true );
            productStyle.setFont ( textFont );

            CellStyle baseProductValueStyle  = workbook.createCellStyle ( );
            baseProductValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueStyle.setWrapText ( true );
            baseProductValueStyle.setFont ( textHeadFont );
            baseProductValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductValueItalicStyle  = workbook.createCellStyle ( );
            baseProductValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueItalicStyle.setWrapText ( true );
            baseProductValueItalicStyle.setFont ( textHeadFontItalic );
            baseProductValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle lotNumberStyle  = workbook.createCellStyle ( );
            lotNumberStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderTop ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderRight ( CellStyle.BORDER_THIN );
            lotNumberStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            lotNumberStyle.setWrapText ( true );
            lotNumberStyle.setFont ( textFont );

            CellStyle productValueStyle  = workbook.createCellStyle ( );
            productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueStyle.setWrapText ( true );
            productValueStyle.setFont ( textFont );
            productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle productValueItalicStyle  = workbook.createCellStyle ( );
            productValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueItalicStyle.setWrapText ( true );
            productValueItalicStyle.setFont ( textFontItalic );
            productValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            int month1TotalQty  = 0;
            int month2TotalQty  = 0;
            int month3TotalQty  = 0;
            int month4TotalQty  = 0;
            int month5TotalQty  = 0;
            int month6TotalQty  = 0;
            int month7TotalQty  = 0;
            int month8TotalQty  = 0;
            int month9TotalQty  = 0;
            int month10TotalQty = 0;
            int month11TotalQty = 0;
            int month12TotalQty = 0;
            //SETTING HEADINGS
            String headers[] = new String[ 15 ];
            headers[0] = "Part Number";
            headers[1] = "    Vendor Name     ";
            headers[2] = "Lot No#";
            Calendar calDate = new GregorianCalendar();
            calDate.setTime(new Date());
            for (int k = 0; k < 12; k++)
            {
                headers[k+3] = StringUtils.formatDateToString(calDate.getTime(),"MMM yyyy");
                calDate.add( Calendar.MONTH, 1 );
            }
            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)(headers.length-1), titleStyle );
            setCellValue ( cell, "Wip Flush - " + heading + " Report By Product " + holdHeading, titleStyle );

            row = sheet.createRow ( 2 );
            for ( int i = 0; i < headers.length; i++ )
            {
                cell    = row.createCell( (short)i );
                setCellValue ( cell, headers[i], headerStyle );
                int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
                sheet.setColumnWidth( (short)i, (short) ( ( ( width + 2 ) * 256 ) ) );
            }

            int nextRowNumber   = 3;
            int nextColumnIdx = 0;
            for ( int i = 0; i < baseProductListSize; i++ )
            {
                WipFlushByMonth baseProduct    =  ( WipFlushByMonth )baseProductList.get ( i );
                row = sheet.createRow( nextRowNumber++ );
                month1TotalQty  += baseProduct.getMonth1Qty();
                month2TotalQty  += baseProduct.getMonth2Qty();
                month3TotalQty  += baseProduct.getMonth3Qty();
                month4TotalQty  += baseProduct.getMonth4Qty();
                month5TotalQty  += baseProduct.getMonth5Qty();
                month6TotalQty  += baseProduct.getMonth6Qty();
                month7TotalQty  += baseProduct.getMonth7Qty();
                month8TotalQty  += baseProduct.getMonth8Qty();
                month9TotalQty  += baseProduct.getMonth9Qty();
                month10TotalQty += baseProduct.getMonth10Qty();
                month11TotalQty += baseProduct.getMonth11Qty();
                month12TotalQty += baseProduct.getMonth12Qty();
                nextColumnIdx = 0;
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getPartNumber(), baseProductStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth1Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth2Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth3Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth4Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth5Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth6Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth7Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth8Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth9Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth10Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth11Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth12Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );

                HashMap childMap    = baseProduct.getChildMap();
                Set keySet  = childMap.keySet();
                Iterator keyIterator = keySet.iterator();
                while ( keyIterator.hasNext() )
                {
                    WipFlushByMonth product    = ( WipFlushByMonth )childMap.get( keyIterator.next() );
                    row = sheet.createRow( nextRowNumber++ );
                    nextColumnIdx = 0;
                    setCellValue( row, (short)nextColumnIdx++, product.getPartNumber(), productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth1Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth2Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth3Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth4Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth5Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth6Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth7Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth8Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth9Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth10Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth11Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, product.getMonth12Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                    HashMap vendorMap = product.getChildMap();
                    if( vendorMap != null && "display=block".equalsIgnoreCase(vendorExpand))
                    {
                        Iterator vendorIt = vendorMap.keySet().iterator();
                        while( vendorIt.hasNext() )
                        {
                            WipFlushByMonth vendor = (WipFlushByMonth)vendorMap.get( vendorIt.next() );
                            row = sheet.createRow( nextRowNumber++ );
                            nextColumnIdx = 0;
                            setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            if( vendor.getVendor() != null ){
                                setCellValue( row, (short)nextColumnIdx++, vendor.getVendor(), productStyle  );
                            }
                            else{
                                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            }
                            setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth1Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth2Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth3Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth4Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth5Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth6Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth7Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth8Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth9Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth10Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth11Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, vendor.getMonth12Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                            HashMap lotMap = vendor.getChildMap();
                            if( lotMap != null && "display=block".equalsIgnoreCase(lotExpand) )
                            {
                                Iterator lotIt = lotMap.keySet().iterator();
                                while( lotIt.hasNext() )
                                {
                                    WipFlushByMonth lot = (WipFlushByMonth)lotMap.get( lotIt.next() );
                                    row = sheet.createRow( nextRowNumber++ );
                                    nextColumnIdx = 0;
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    if( lot.getLotNumber() != null)
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, lot.getLotNumber(), lotNumberStyle  );
                                    }
                                    else{
                                        setCellValue( row, (short)nextColumnIdx++, "", lotNumberStyle  );
                                    }
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth1Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth2Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth3Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth4Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth5Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth6Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth7Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth8Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth9Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth10Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth11Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth12Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                               }
                            }
                        }
                    }
                }
            }
            row = sheet.createRow( nextRowNumber++ );
            nextColumnIdx = 0;
            setCellValue( row, (short)nextColumnIdx++, "Grand Total", headerStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, month1TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month2TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month3TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month4TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month5TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month6TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month7TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month8TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month9TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month10TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month11TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month12TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
        }
        return workbook;
    }
    public static Workbook getWorkbookForWipFlushShippableByVendorWeek ( ArrayList vendorList, String heading, String holdHeading, int startingWWNumber, int wwYear, int noOfWeeks, String productExpand, String lotExpand )
    {
        noOfWeeks = (noOfWeeks == 0) ? 13 : noOfWeeks;
        Workbook workbook   = null;
        Sheet   sheet       = null;
        int vendorListSize = ( vendorList == null ) ? 0 : vendorList.size();
        if ( vendorListSize > 0 )
        {
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                workbook    = new XSSFWorkbook();
            }
            else
            {
                workbook    = new HSSFWorkbook();
            }
            CreationHelper ch = workbook.getCreationHelper();
            DataFormat df = ch.createDataFormat();

            sheet       = workbook.createSheet( "Wip Flush Shippable By Vendor" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 10 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFontItalic = workbook.createFont();
            headerFontItalic.setFontHeightInPoints ( ( short ) 10 );
            headerFontItalic.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFontItalic.setFontName( "verdana" );
            headerFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            headerFontItalic.setItalic( true );

            Font textHeadFont = workbook.createFont();
            textHeadFont.setFontHeightInPoints ( ( short ) 10 );
            textHeadFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFont.setFontName( "verdana" );
            textHeadFont.setColor ( IndexedColors.BLUE.getIndex() );

            Font textHeadFontItalic = workbook.createFont();
            textHeadFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textHeadFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFontItalic.setFontName( "verdana" );
            textHeadFontItalic.setColor ( IndexedColors.BLUE.getIndex() );
            textHeadFontItalic.setItalic( true );

            Font textFont = workbook.createFont();
            textFont.setFontHeightInPoints ( ( short ) 10 );
            textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFont.setFontName( "verdana" );
            textFont.setColor ( IndexedColors.BLACK.getIndex());

            Font textFontItalic = workbook.createFont();
            textFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFontItalic.setFontName( "verdana" );
            textFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            textFontItalic.setItalic( Boolean.TRUE.booleanValue() );

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle headerValueStyle  = workbook.createCellStyle ( );
            headerValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueStyle.setWrapText ( true );
            headerValueStyle.setFont ( headerFont );
            headerValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle headerValueItalicStyle  = workbook.createCellStyle ( );
            headerValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueItalicStyle.setWrapText ( true );
            headerValueItalicStyle.setFont ( headerFontItalic );
            headerValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductStyle  = workbook.createCellStyle ( );
            baseProductStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            baseProductStyle.setWrapText ( true );
            baseProductStyle.setFont ( textHeadFont );

            CellStyle productStyle  = workbook.createCellStyle ( );
            productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            productStyle.setWrapText ( true );
            productStyle.setFont ( textFont );

            CellStyle baseProductValueStyle  = workbook.createCellStyle ( );
            baseProductValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueStyle.setWrapText ( true );
            baseProductValueStyle.setFont ( textHeadFont );
            baseProductValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductValueItalicStyle  = workbook.createCellStyle ( );
            baseProductValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueItalicStyle.setWrapText ( true );
            baseProductValueItalicStyle.setFont ( textHeadFontItalic );
            baseProductValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle lotNumberStyle  = workbook.createCellStyle ( );
            lotNumberStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderTop ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderRight ( CellStyle.BORDER_THIN );
            lotNumberStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            lotNumberStyle.setWrapText ( true );
            lotNumberStyle.setFont ( textFont );

            CellStyle productValueStyle  = workbook.createCellStyle ( );
            productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueStyle.setWrapText ( true );
            productValueStyle.setFont ( textFont );
            productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle productValueItalicStyle  = workbook.createCellStyle ( );
            productValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueItalicStyle.setWrapText ( true );
            productValueItalicStyle.setFont ( textFontItalic );
            productValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            //SETTING HEADINGS
            String headers[] = new String[ noOfWeeks+3 ];
            headers[0] = "    Vendor Name     ";
            headers[1] = "Part Number";
            headers[2] = "Lot No#";
            int wwNumber = startingWWNumber;
            for (int k = 0; k < noOfWeeks; k++)
            {
                if (wwNumber == 53 && k > 0)
                {
                    Calendar cal = Calendar.getInstance();
                    cal.set(wwYear, Calendar.DECEMBER, 31);
                    WorkWeek workWeek = WorkWeek.getWorkWeekInstance(cal.getTime());
                    if (workWeek.getCurrentWWNumber() != wwNumber) //Work week 53 not present
                    {
                        wwNumber = 1;
                        wwYear = wwYear + 1;
                    }
                } else if (wwNumber > 53) {
                    wwNumber = 1;
                    wwYear = wwYear + 1;
                }
                if (k != 25)
                {
                    headers[k+3] = "WW-" + wwNumber;
                }
                else
                {
                    headers[k+3] = "WW-" + wwNumber + " to ~";
                }
                wwNumber++;
            }
            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)(headers.length-1), titleStyle );
            setCellValue ( cell, "Wip Flush - " + heading + " Report By Vendor " + holdHeading, titleStyle );

            row = sheet.createRow ( 2 );
            for ( int i = 0; i < headers.length; i++ )
            {
                cell    = row.createCell( (short)i );
                setCellValue ( cell, headers[i], headerStyle );
                int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
                sheet.setColumnWidth( (short)i, (short) ( ( ( width + 2 ) * 256 ) ) );
            }

            int nextRowNumber   = 3;
            int nextColumnIdx = 0;
            int[] allWeekTotalQty = new int[26];
            for ( int i = 0; i < vendorListSize; i++ )
            {
                WipFlushByDate vendor    =  ( WipFlushByDate )vendorList.get ( i );
                row = sheet.createRow( nextRowNumber++ );
                nextColumnIdx = 0;
                setCellValue( row, (short)nextColumnIdx++, vendor.getVendor(), baseProductStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                for ( int k = 0; k < noOfWeeks; k++ )
                {
                    allWeekTotalQty[k] = allWeekTotalQty[k] + vendor.getAllWeekQty()[k];
                    setCellValue( row, (short)nextColumnIdx++, vendor.getAllWeekQty()[ k ], baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                }

                HashMap childMap    = vendor.getChildMap();
                Set keySet  = childMap.keySet();
                Iterator keyIterator = keySet.iterator();
                while ( keyIterator.hasNext() )
                {
                    WipFlushByDate baseProduct    = ( WipFlushByDate )childMap.get( keyIterator.next() );
                    row = sheet.createRow( nextRowNumber++ );
                    nextColumnIdx = 0;
                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getPartNumber(), productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                    for ( int k = 0; k < noOfWeeks; k++ )
                    {
                        setCellValue( row, (short)nextColumnIdx++, baseProduct.getAllWeekQty()[ k ], productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    }

                    HashMap productMap = baseProduct.getChildMap();
                    if( productMap != null  && "display=block".equalsIgnoreCase(productExpand))
                    {
                        Iterator productIt = productMap.keySet().iterator();
                        while( productIt.hasNext() )
                        {
                            WipFlushByDate product = (WipFlushByDate)productMap.get( productIt.next() );
                            row = sheet.createRow( nextRowNumber++ );
                            nextColumnIdx = 0;
                            setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            if( vendor.getVendor() != null ){
                                setCellValue( row, (short)nextColumnIdx++, product.getPartNumber(), productStyle  );
                            }
                            else{
                                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            }
                            setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                            for ( int k = 0; k < noOfWeeks; k++ )
                            {
                                setCellValue( row, (short)nextColumnIdx++, product.getAllWeekQty()[k], productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            }
                            HashMap lotMap = product.getChildMap();
                            if( lotMap != null && "display=block".equalsIgnoreCase(lotExpand))
                            {
                                Iterator lotIt = lotMap.keySet().iterator();
                                while( lotIt.hasNext() )
                                {
                                    WipFlushByDate lot = (WipFlushByDate)lotMap.get( lotIt.next() );
                                    row = sheet.createRow( nextRowNumber++ );
                                    nextColumnIdx = 0;
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    if( lot.getLotNumber() != null)
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, lot.getLotNumber(), lotNumberStyle  );
                                    }
                                    else{
                                        setCellValue( row, (short)nextColumnIdx++, "", lotNumberStyle  );
                                    }
                                    for ( int k = 0; k < noOfWeeks; k++ )
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, lot.getAllWeekQty()[k], productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    }
                               }
                            }
                        }
                    }
                }
            }
            row = sheet.createRow( nextRowNumber++ );
            nextColumnIdx = 0;
            setCellValue( row, (short)nextColumnIdx++, "Grand Total", headerStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            for ( int k = 0; k < noOfWeeks; k++ )
            {
                setCellValue( row, (short)nextColumnIdx++, allWeekTotalQty[k], headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            }
        }
        return workbook;
    }
    public static Workbook getWorkbookForWipFlushShippableByVendorMonth ( ArrayList vendorList, String heading, String holdHeading, String productExpand, String lotExpand )
    {
        Workbook workbook   = null;
        Sheet   sheet       = null;
        int vendorListSize = ( vendorList == null ) ? 0 : vendorList.size();
        if ( vendorListSize > 0 )
        {
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                workbook    = new XSSFWorkbook();
            }
            else
            {
                workbook    = new HSSFWorkbook();
            }
            CreationHelper ch = workbook.getCreationHelper();
            DataFormat df = ch.createDataFormat();

            sheet       = workbook.createSheet( "Wip Flush Shippable By Vendor" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 10 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex());

            Font headerFontItalic = workbook.createFont();
            headerFontItalic.setFontHeightInPoints ( ( short ) 10 );
            headerFontItalic.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFontItalic.setFontName( "verdana" );
            headerFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            headerFontItalic.setItalic( true );

            Font textHeadFont = workbook.createFont();
            textHeadFont.setFontHeightInPoints ( ( short ) 10 );
            textHeadFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFont.setFontName( "verdana" );
            textHeadFont.setColor ( IndexedColors.BLUE.getIndex() );

            Font textHeadFontItalic = workbook.createFont();
            textHeadFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textHeadFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textHeadFontItalic.setFontName( "verdana" );
            textHeadFontItalic.setColor ( IndexedColors.BLUE.getIndex() );
            textHeadFontItalic.setItalic( true );

            Font textFont = workbook.createFont();
            textFont.setFontHeightInPoints ( ( short ) 10 );
            textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFont.setFontName( "verdana" );
            textFont.setColor ( IndexedColors.BLACK.getIndex());

            Font textFontItalic = workbook.createFont();
            textFontItalic.setFontHeightInPoints ( ( short ) 10 );
            textFontItalic.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            textFontItalic.setFontName( "verdana" );
            textFontItalic.setColor ( IndexedColors.BLACK.getIndex());
            textFontItalic.setItalic( Boolean.TRUE.booleanValue() );

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle headerValueStyle  = workbook.createCellStyle ( );
            headerValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueStyle.setWrapText ( true );
            headerValueStyle.setFont ( headerFont );
            headerValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle headerValueItalicStyle  = workbook.createCellStyle ( );
            headerValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            headerValueItalicStyle.setWrapText ( true );
            headerValueItalicStyle.setFont ( headerFontItalic );
            headerValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductStyle  = workbook.createCellStyle ( );
            baseProductStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            baseProductStyle.setWrapText ( true );
            baseProductStyle.setFont ( textHeadFont );

            CellStyle productStyle  = workbook.createCellStyle ( );
            productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            productStyle.setWrapText ( true );
            productStyle.setFont ( textFont );

            CellStyle baseProductValueStyle  = workbook.createCellStyle ( );
            baseProductValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueStyle.setWrapText ( true );
            baseProductValueStyle.setFont ( textHeadFont );
            baseProductValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle baseProductValueItalicStyle  = workbook.createCellStyle ( );
            baseProductValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            baseProductValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            baseProductValueItalicStyle.setWrapText ( true );
            baseProductValueItalicStyle.setFont ( textHeadFontItalic );
            baseProductValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle lotNumberStyle  = workbook.createCellStyle ( );
            lotNumberStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderTop ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            lotNumberStyle.setBorderRight ( CellStyle.BORDER_THIN );
            lotNumberStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            lotNumberStyle.setWrapText ( true );
            lotNumberStyle.setFont ( textFont );

            CellStyle productValueStyle  = workbook.createCellStyle ( );
            productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueStyle.setWrapText ( true );
            productValueStyle.setFont ( textFont );
            productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle productValueItalicStyle  = workbook.createCellStyle ( );
            productValueItalicStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderTop ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setBorderRight ( CellStyle.BORDER_THIN );
            productValueItalicStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            productValueItalicStyle.setWrapText ( true );
            productValueItalicStyle.setFont ( textFontItalic );
            productValueItalicStyle.setDataFormat( df.getFormat( "#,##0" ) );

            int month1TotalQty  = 0;
            int month2TotalQty  = 0;
            int month3TotalQty  = 0;
            int month4TotalQty  = 0;
            int month5TotalQty  = 0;
            int month6TotalQty  = 0;
            int month7TotalQty  = 0;
            int month8TotalQty  = 0;
            int month9TotalQty  = 0;
            int month10TotalQty = 0;
            int month11TotalQty = 0;
            int month12TotalQty = 0;
            //SETTING HEADINGS
            String headers[] = new String[ 15 ];
            headers[0] = "    Vendor Name     ";
            headers[1] = "Part Number";
            headers[2] = "Lot No#";
            Calendar calDate = new GregorianCalendar();
            calDate.setTime(new Date());
            for (int k = 0; k < 12; k++)
            {
                headers[k+3] = StringUtils.formatDateToString(calDate.getTime(),"MMM yyyy");
                calDate.add( Calendar.MONTH, 1 );
            }
            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)(headers.length-1), titleStyle );
            setCellValue ( cell, "Wip Flush - " + heading + " Report By Vendor " + holdHeading, titleStyle );

            row = sheet.createRow ( 2 );
            for ( int i = 0; i < headers.length; i++ )
            {
                cell    = row.createCell( (short)i );
                setCellValue ( cell, headers[i], headerStyle );
                int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
                sheet.setColumnWidth( (short)i, (short) ( ( ( width + 2 ) * 256 ) ) );
            }

            int nextRowNumber   = 3;
            int nextColumnIdx = 0;
            for ( int i = 0; i < vendorListSize; i++ )
            {
                WipFlushByMonth vendor    =  ( WipFlushByMonth )vendorList.get ( i );
                row = sheet.createRow( nextRowNumber++ );
                month1TotalQty  += vendor.getMonth1Qty();
                month2TotalQty  += vendor.getMonth2Qty();
                month3TotalQty  += vendor.getMonth3Qty();
                month4TotalQty  += vendor.getMonth4Qty();
                month5TotalQty  += vendor.getMonth5Qty();
                month6TotalQty  += vendor.getMonth6Qty();
                month7TotalQty  += vendor.getMonth7Qty();
                month8TotalQty  += vendor.getMonth8Qty();
                month9TotalQty  += vendor.getMonth9Qty();
                month10TotalQty += vendor.getMonth10Qty();
                month11TotalQty += vendor.getMonth11Qty();
                month12TotalQty += vendor.getMonth12Qty();
                nextColumnIdx = 0;
                setCellValue( row, (short)nextColumnIdx++, vendor.getVendor(), baseProductStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth1Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth2Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth3Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth4Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth5Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth6Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth7Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth8Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth9Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth10Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth11Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, vendor.getMonth12Qty(), baseProductValueStyle, Cell.CELL_TYPE_NUMERIC  );

                HashMap childMap    = vendor.getChildMap();
                Set keySet  = childMap.keySet();
                Iterator keyIterator = keySet.iterator();
                while ( keyIterator.hasNext() )
                {
                    WipFlushByMonth baseProduct    = ( WipFlushByMonth )childMap.get( keyIterator.next() );
                    row = sheet.createRow( nextRowNumber++ );
                    nextColumnIdx = 0;
                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getPartNumber(), productStyle  );
                    setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth1Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth2Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth3Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth4Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth5Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth6Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth7Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth8Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth9Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth10Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth11Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    setCellValue( row, (short)nextColumnIdx++, baseProduct.getMonth12Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                    HashMap productMap = baseProduct.getChildMap();
                    if( productMap != null  && "display=block".equalsIgnoreCase(productExpand))
                    {
                        Iterator productIt = productMap.keySet().iterator();
                        while( productIt.hasNext() )
                        {
                            WipFlushByMonth product = (WipFlushByMonth)productMap.get( productIt.next() );
                            row = sheet.createRow( nextRowNumber++ );
                            nextColumnIdx = 0;
                            setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            if( vendor.getVendor() != null ){
                                setCellValue( row, (short)nextColumnIdx++, product.getPartNumber(), productStyle  );
                            }
                            else{
                                setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                            }
                            setCellValue( row, (short)nextColumnIdx++, "", productValueStyle  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth1Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth2Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth3Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth4Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth5Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth6Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth7Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth8Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth9Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth10Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth11Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                            setCellValue( row, (short)nextColumnIdx++, product.getMonth12Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );

                            HashMap lotMap = product.getChildMap();
                            if( lotMap != null && "display=block".equalsIgnoreCase(lotExpand))
                            {
                                Iterator lotIt = lotMap.keySet().iterator();
                                while( lotIt.hasNext() )
                                {
                                    WipFlushByMonth lot = (WipFlushByMonth)lotMap.get( lotIt.next() );
                                    row = sheet.createRow( nextRowNumber++ );
                                    nextColumnIdx = 0;
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    setCellValue( row, (short)nextColumnIdx++, "", productStyle  );
                                    if( lot.getLotNumber() != null)
                                    {
                                        setCellValue( row, (short)nextColumnIdx++, lot.getLotNumber(), lotNumberStyle  );
                                    }
                                    else{
                                        setCellValue( row, (short)nextColumnIdx++, "", lotNumberStyle  );
                                    }
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth1Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth2Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth3Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth4Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth5Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth6Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth7Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth8Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth9Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth10Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth11Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                                    setCellValue( row, (short)nextColumnIdx++, lot.getMonth12Qty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                               }
                            }
                        }
                    }
                }
            }
            row = sheet.createRow( nextRowNumber++ );
            nextColumnIdx = 0;
            setCellValue( row, (short)nextColumnIdx++, "Grand Total", headerStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, "", headerValueStyle  );
            setCellValue( row, (short)nextColumnIdx++, month1TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month2TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month3TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month4TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month5TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month6TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month7TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month8TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month9TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month10TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month11TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
            setCellValue( row, (short)nextColumnIdx++, month12TotalQty, headerValueStyle, Cell.CELL_TYPE_NUMERIC  );
        }
        return workbook;
    }
}